Protecting Individual Worksheets, by User

by Allen Wyatt
(last updated August 6, 2022)

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

Hiding Rows Based on a Cell Value

This tip contains a macro to hide rows that contain data you don't want to see.

Discover More

Cleaning Text

You can use the CLEAN worksheet function to remove any non-printable characters from a cell. This can come in handy when ...

Discover More

Searching for Leading Apostrophes

Take a look at the Formula bar when you select a cell that contains text, and you may see an apostrophe at the beginning ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Preventing Someone from Recreating a Protected Worksheet

When you share a protected workbook with other people, you may not want them to get around the protection by creating a ...

Discover More

Functioning Check Boxes in a Protected Worksheet

Want to protect a worksheet but maintain the ability to make changes to the check boxes you add to the worksheet? Here is ...

Discover More

Hiding and Protecting Columns

Want to hide certain columns within a worksheet so the contents are not visible to others? The answer lies in formatting ...

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 seven minus 2?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


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.