Protecting Individual Worksheets, by User

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.

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