Password protecting worksheets

  • I would like to password protect each individual worksheet so different users can use different sheets. Can this be done?:beergrin::beergrin:

  • Yes, just select a sheet, click Tools-Protection-Protect Sheet - make a password, repeat on each other sheet with a different password.
    Or is this a more complicated question than I thought? Would you like a macro with that? :)

    Hope this is what you're looking for... Doug

  • Hi Chester,

    As Doug indicated, your question isn't all that clear. The following is based upon my interpretation:

    In the attached workbook all of the sheets, except one, are hidden when the workbook is closed. When it is opened a userform appears asking for a username and password. This is checked to a sheet named 'Login' and if the details are correct the sheet named on the 'Login' sheet, alongside the username and password, is opened.

    Points to note:
    1. Hastily prepared example only - amend to suit your requirements.
    2. Apply a VBAProject password of your choice.
    3. Keep in mind that Excel was never designed to have 'secure' workbooks/worksheets. If somebody is determined they will 'get in'. (The example workbook will not fool any VBA coders).

    Repost with any queries (or to tell me that I misunderstood your question ;) ).

    PS: Go to the VBE and unhide the 'Login' worksheet to see the sample usernames and passwords.

  • Hi Chester,

    Just another interpretation:

    If you know the 'Office UserName' of your spreadsheet users you can add some code to the Workbook_SheetActivate event to automate sheet protection.

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim User As String
    Dim UnLockSheet As Boolean
    Const SheetPassWord As String = "Password"

    User = Application.UserName

    Select Case ActiveSheet.Name
    Case "User1's Sheet"
    If User = "User1" Then UnLockSheet = True
    Case "User2's Sheet"
    If User = "User2" Then UnLockSheet = True
    Case "User3's Sheet"
    If User = "User3" Then UnLockSheet = True
    End Select

    If UnLockSheet Then
    ActiveSheet.Unprotect Password:=SheetPassWord
    ActiveSheet.Protect Password:=SheetPassWord, DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If

    End Sub

    This is very low level protection but works fine for the general user. Just alter the sheet names to match the user and the sheet will be unprotected. Change password to whatever you want.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!