Excel VBA hide worksheet with password (xlVeryHidden)

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.

  1. Add password to workbook. Users are asked to open the Excel file with password.
  2. Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
  3. Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
  4. 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.

hide_worksheet_4

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)

hide_worksheet_5

You can only unhide through VBE, by changing xlSheetVeryHidden to xlSheetVisible

hide_worksheet_2

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)

hide_worksheet_1

Outbound References

https://support.microsoft.com/en-us/kb/213609

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *