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.