Announcement

Collapse
No announcement yet.

Read only open

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Read only open



    I would like to know if you can open an excel workbook in read only if the computer user and computer name doesn’t match existing criteria. Example: if the main person to open the wb is computer name is WS2019, and the logon name is wsmith, but the computer name opening the wb is RS2019 and the login name is rgraves, then I need the excel wb to open up in “read only” so the the excel wb cannot be saved and if changes need to be made, only wsmith can open it and make the changes. I have multi-users that use this excel workbook everyday and if changes need made, only I know how to make them. I am not always the first to open it up and so if changes needs made, I have to get everyone to close the wb so I can open it and make the changes. I also don’t want the possibility that the wb can be saved. Thank you in advance for any and all help.

  • #2
    That would require VBA, but to get round it a user would only need to not enable macros.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Unfortunately, macros are used throughout the workbook, I was hoping to have some type of VBA that would identify the user and if not the main user, then open the workbook as “read only” (just like someone else had the workbook open).

      Comment


      • #4
        Use the WorkBook open event. You need to know the user name to open it.

        Code:
        Option Explicit
        
        Private Sub Workbook_Open()
        If Environ("USERNAME") <> " enter name here" Then ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
        End Sub
        Environ("username") is the user name which you log into Windows with. Application.UserName is the user name which is set by the user when they run the program for the first time or whatever it was if you go to TOOLS > OPTIONS > GENERAL and set it under User Name.

        Alternatively you could ask the user to provide a password with an inputbox or userform
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          That will probably work, I will check it tomorrow, one other thing is the pop up that usually occurs to ask if you want to open as read only or notify to be able to read-write, is there any other VBA to restrict this pop up from showing. Thank you so much, in advance.

          Comment


          • #6

            Try this

            Code:
            Option Explicit
            
            Private Sub Workbook_Open()
                Application.AskToUpdateLinks = False
                If Environ("USERNAME") <> " enter name here" Then ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
            End Sub
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              This still produces the pop up for the user to click on "READ ONLY", but it also creates a RUNTIME ERROR 1004 pop-up window. I'm not sure what to do now.
              Thanks in advance.

              Kenny

              Comment


              • #8
                try it this way

                Code:
                 Private Sub Workbook_Open()    
                If Environ("USERNAME") <> " enter name here" Then ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
                End Sub
                Last edited by royUK; 7 hours ago.
                Hope that Helps

                Roy

                New users should read the Forum Rules before posting

                For free Excel tools & articles visit my web site

                RoyUK's Web Site

                royUK's Database Form

                Where to paste code from the Forum

                About me.

                Comment


                • #9
                  I get the same result. The "READ ONLY" pop up shows up and then the RUNTIME ERROR 1004, we click on "END" and then the workbook opens.

                  Kenny

                  Comment


                  • #10
                    Are you sure that the username is correct?
                    Hope that Helps

                    Roy

                    New users should read the Forum Rules before posting

                    For free Excel tools & articles visit my web site

                    RoyUK's Web Site

                    royUK's Database Form

                    Where to paste code from the Forum

                    About me.

                    Comment


                    • #11
                      Ok, I had the correct username, but I found out that if I am not in the workbook, that this works fine, but if I am the first to open the workbook that it creates the runtime error. Can the errors be eliminated if I have the workbook open?

                      Kenny

                      Comment


                      • #12


                        Sharing workbooks is always problematic. Maybe

                        Code:
                        Private Sub Workbook_Open()
                         On Error Resume Next
                        If Environ("USERNAME") <> " enter name here" Then ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
                        End Sub
                        Hope that Helps

                        Roy

                        New users should read the Forum Rules before posting

                        For free Excel tools & articles visit my web site

                        RoyUK's Web Site

                        royUK's Database Form

                        Where to paste code from the Forum

                        About me.

                        Comment

                        Working...
                        X