Password Protect and Hide Excel Tabs (VBA)

Recently, I was tasked with developing a single workbook that could be distributed to various employees, but there was a catch… Each employee should only be able to access the tabs that were relevant to them.

The native Excel protection does not allow for individually password protecting tabs, so we this would have to be done through VBA.

I cannot claim credit for this code, but since it is very useful for this project and I imagine that I will be using it again, I decided to post it to the site.

Sub author()
Dim i_pwd As String
i_pwd = InputBox("Please Enter your Password")
If i_pwd = "" Then
Exit Sub
End If
    Select Case LCase(i_pwd)
    'set the administrator password
 'the administrator can see all sheets
 Case Is = "secret"
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next ws
        MsgBox "As an Administrator all sheets are now visible to you.", vbInformation, ""
 'set the first user role password 
    Case Is = "everyone"
 'set the worksheets that are visible with this password
        Worksheets("Sheet2").Visible = True
        Worksheets("Sheet3").Visible = True
    Case Else
        MsgBox "Incorrect password; no action taken.", vbInformation, ""
    End Select
End Sub

One caveat to this code is that if you don’t password protect your VBA module, any user can hit Alt+F11 and view the code, with the passwords plain as day.