Protecting Individual Worksheets, by User

Written by Allen Wyatt (last updated August 6, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003


7

Excel allows you to protect individual worksheets, as you have learned in other issues of ExcelTips. (You choose Tools | Protection | Protect Sheet.) You can use this approach to protect individual worksheets independently, using different passwords. This means that one user could make changes to one worksheet using one password, and another could use a different password to make changes to the other worksheet.

What if you want to limit access to the worksheets entirely, however? What if you don't even want an unauthorized user to see the other worksheet? This need is a bit trickier to accommodate, but it can be done. The basic approach would be as follows:

  1. Set up a workbook that has three worksheets: One that will always be open, one for user 1, and the third for user 2.
  2. Hide the worksheets for user 1 and user 2.
  3. Create a form that appears whenever the workbook is opened, asking for a user name and password.
  4. Create macro code that unlocks and displays the proper worksheet based on the user name and password.
  5. Protect the entire workbook (Tools | Protection | Protect Workbook).

Steps 1, 2, and 5 are easy enough to do, and have been covered in other issues of ExcelTips. The crux of this approach, however, is steps 3 and 4. You can create a user form by following these steps:

  1. Press Alt+F11 to display the VBA Editor.
  2. In the VBA Editor, choose User Form from the Insert menu. A new, blank user form displays, along with the form toolbox.
  3. Using the controls in the form toolbox, add a TextBox control where the user will enter their user name.
  4. Change the properties for the TextBox control so that its Name is txtUser.
  5. Using the controls in the form toolbox, add a TextBox control where the user will enter their password.
  6. Change the properties for the TextBox control so that its Name is txtPass.
  7. Just under the TextBox controls, add a CommandButton control.
  8. Change the properties for the CommandButton control so its Name is btnOK and its Caption is OK.

With your user form created you are ready to associate macro code with the controls you just placed. Make sure the user form is selected and press F7 to display the Code window for the form. The window may contain a line or two of automatically generated code. Replace this with the following code:

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean

    bOK2Use = False
    bError = True
    If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
        bError = False
        Select Case txtUser.Text
            Case "user1"
                sSName = "u1sheet"
                If txtPass.Text <> "u1pass" Then bError = True
            Case "user2"
                sSName = "u2sheet"
                If txtPass.Text <> "u2pass" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If

        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect (txtPass.Text)
        Sheets(sSName).Activate

        bOK2Use = True
        Unload UserForm1
    End If
End Sub

Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub

The above code does several things. Notice that there are two procedures: a longer one that runs when the user clicks on the OK button in the form, and another that runs when the form is terminated. When the user clicks on the OK button, the procedure checks to make sure that the combination of the user name and password is correct. If it is not, then the user is notified. If it is, then the authorized sheet name is stored in a document variable and the appropriate sheet is displayed and unprotected.

If you want to change the acceptable user names, sheet names, and passwords, you can do so by making the desired changes in the Select Case structure near the top of this macro code.

The second macro in this code (UserForm_Terminate) comes into play if the user tries to simply dismiss your form without entering a user name and password. In this instance, if the authorization process was not previously completed, then the workbook is simply closed.

In addition to the above code, you will also need to add the following macros to the workbook itself. These open the user form when the workbook is opened, and protect the worksheet when the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim w As Worksheet
    Dim bSaveIt As Boolean

    bSaveIt = False
    For Each w In Worksheets
        If w.Visible Then
            Select Case w.Name
                Case "u1sheet"
                    w.Protect ("u1pass")
                    w.Visible = False
                    bSaveIt = True
                Case "u2sheet"
                    w.Protect ("u2pass")
                    w.Visible = False
                    bSaveIt = True
            End Select
        End If
    Next w
    If bSaveIt Then
        ActiveWorkbook.CustomDocumentProperties("auth").Delete
        ActiveWorkbook.Save
    End If
End Sub

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "Main" Then
        If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then
            Sh.Visible = False
            MsgBox "You don't have authorization to view that sheet!"
        End If
    End If
End Sub

When the user chooses to close the workbook--they are done with their work--the applicable worksheets are again protected and hidden. (If you change user sheet names and passwords, you will need to change them in the Select Case structure here, as well.) The macro then deletes the appropriate document property and saves the workbook.

Another interesting macro here is the Workbook_SheetActivate procedure. This is included in case where one user tries to use Format | Sheet | Unhide to unhide another user's worksheet. In this case, the user's authorized sheet name (stored in a document variable when the user was originally authorized) is compared to the sheet being displayed. If it doesn't match, then the user isn't allowed to view the worksheet. Note, as well, that this procedure references a worksheet called "Main". This worksheet is the third worksheet mentioned at the beginning of this tip. This worksheet is also the one first displayed when the workbook is opened.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1952) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Sorting by Columns

When you think of sorting Excel data, it is likely that you think of sorting rows. Excel also allows you to sort by ...

Discover More

Generating a List of Dates

When creating tracking documents in Word, you may need to come up with a series of dates in the document. You can type ...

Discover More

Capitalizing Just a Surname

Changing the capitalization of text is, believe it or not, a common task in Excel. Common or not, it can be frustrating ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Locking All Non-Empty Cells

Need to make sure that your worksheet is locked, with only the blank cells accessible to editing? You can do this easily ...

Discover More

Protecting Worksheets

Excel allows data protection for particular cells or a whole worksheet in a shared work environment. Here's how to apply ...

Discover More

Inserting and Deleting Rows in a Protected Worksheet

You've protected and saved your worksheet with explicit instructions that you be allowed to insert and delete rows. But ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2024-01-25 00:22:42

Rong

I am new to vba. Please help me out, I don't know where to put the code "Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim w As Worksheet
Dim bSaveIt As Boolean..."
I put it in module1 of Thisworkbook but it didn't work.
Thank you so much!


2023-02-26 06:34:53

yen

I am new to VBA. Tried to use this code in Excel 2016. However, cant seem to run the code upon opening excel? Where did i do wrong? And yes, i need a Super user to view all sheet. Please help.


2023-02-22 09:33:17

Stuart

Love this tutorial and solution for protecting individual worksheets by user, however I am currently having an issue not being able to change usernames and passwords :(

When i originally set up the workbook i built in spares for future use; User - Spare1 with password - Spare1 for example then when a new user has to be assigned i thought i could just rename, User - Spare1 to User - Barry, password - Spare1 to password - N32)yb for example.

Changing these details within the Select Case structure in both the user form and the workbook result in an error code 1004 saying that username or password is in correct make sure caps lock is on/off.

Any thoughts on what is going on here please?


2022-12-29 12:44:03

Dustin

Would this work for Excel 2019? If not, is there an alternative?


2022-12-14 11:58:55

Barry

@Julien checkout my version at https://www.buzzworks.co.uk/ProtectingWorksheetsbyUser.xlsm it doesn't use forms so may work better on a MAC but I haven't tested it.

@Kinjal my version of this has a user sheet which can make any user an "admin" user who can access all worksheets, and indivial normal users can be allowed ot access as many sheets as you the admininster wants to allow (on a per user basis). Email me for usernames and passwords.


2022-12-13 07:44:54

Kinjal Doshi

This is helpful where different users who are viewing my file, won't be able to see other sheets.. but after adding this macro.. I have to add username and password to unlock each sheet. Can we add a code for Super User ...i.e. me... who has rights to view all the pages? and don't have to enter password for each users?


2022-10-18 18:06:39

Julien

How about Mac users? We don't seem to have the "user form" option in the VBA editor insert choices; is there a way to go around that limitation?


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.