This Excel VBA tutorial explains how to hide worksheet with password (xlVeryHidden).
You may also want to read:
Excel VBA protect worksheet with password
Excel VBA Add password to Excel workbook in folder
Protect VBA code with password
Different kinds of workbook protection
There are several kinds of protection in Excel.
- Add password to workbook. Users are asked to open the Excel file with password.
- Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
- Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
- Protect worksheet (cells) with password. Restrict users to edit the cell
Excel hide worksheet with password
Recently, I created a workbook with several worksheets inside. For each worksheet, I want to send to different party, but I can’t send the whole workbook to everyone because they contain confidential data. Therefore I was thinking about protecting each worksheet with different password so that I can distribute the workbook out in one email, instead of creating several workbooks and distribute several emails.
Step 1 – Create a menu page
Create a worksheet named “menu” that serves as a menu page. In that page, add a button, which can be either a Shape or an ActiveX Button, the purpose of it is to trigger a Macro.
Step 2 – Assign Macro to button
Press ALT+F11 and create a Macro as below. This Macro prompts a user input box that asks user to enter a password. Different password will open a different worksheet. When password is Showall, all worksheets become visible. While password Hideall does the opposite.
Public Sub triggerBtn()
sinput = InputBox("Enter your password")
Select Case sinput
Case "pw1"
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Activate
Case "pw2"
Sheets("Sheet2").Visible = True
Sheets("Sheet2").Activate
Case "pw3"
Sheets("Sheet3").Visible = True
Sheets("Sheet3").Activate
Case "Showall"
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = True
Next
Case "Hideall"
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "menu" Then
ws.Visible = xlVeryHidden
End If
Next
Case Else
dummy = MsgBox("Incorrect Password", vbExclamation)
End Select
End Sub
Visible status xlVeryHidden means that you cannot unhide the worksheet in normal worksheet View (as if it doesn’t exist)
You can only unhide through VBE, by changing xlSheetVeryHidden to xlSheetVisible
Step 3 – Set password for VBA project
Now that we have set password for each worksheet, but everyone clicks on ALT+F11 can look at the password or unhide the worksheets as explained above.
In this final step, we set password to prevent users to view the Project.
In VBE (Alt+F11), navigate to Tools > VBAProject Properties > Protection > check the box “Lock project for viewing” > enter password (close the workbook and reopen to take effect)
Outbound References
https://support.microsoft.com/en-us/kb/213609