Excel: Hiding sheets using VBA and a password
I’ve always like secret stuff and have been interested in 1960’s spy technology. That may be why I like the idea of hiding sheets in an Excel workbook from other users. In Excel sheets can be hidden (xlsSheetsHidden) or very hidden (xlsSheetsVeryHidden). When you right click on the sheet label and select “Hide” the sheet is hidden. Sheets that have been hidden this way can be made visible by right clicking on one of the sheets labels and selecting “Unhide”. Sheets that are very hidden will not show up in the menu of available hidden sheets.
Very hidden sheets can only be made visible through the VBA editor.
Automating with VBA
At work I hide sheets in some spreadsheets that have sheets with data that I sometimes need to use but will be of no use to others and this should avoid anyone accidentally corrupting any of the data in those sheets. It may also be useful for a control panel sheet that I don’t want others playing with. So I wrote some simple code to do this.
I made a simple workbook to try this out for myself. There are two macros associated with workbook. The first is called HideSheets. It is used to toggle the visibility of Sheet2 and Sheet3 between visible and very hidden. The user must supply a password. The default password is set to “password”, but it can be easily changed in the VBA code.
The second macro called Auto_Close runs automatically when the user attempts to close the book. It hides the sheets set in that macro; in this case it’s set to hide the same sheets, Sheet2 and Sheet3. The user is then prompted to save the book. The purpose of this is to hide the sheets in case you forget to hide them before closing.
You can download a copy of the HidingSheetsUsingPassword spreadsheet in Office 2007 format. It contains the same code as below.
Sub HideSheets() ' This macro toggles worksheets listed between visible and very hidden ' and requires a password to run Dim myPassword As String myPassword = "password" ' Set password here Password = InputBox("Enter Password") If Password = "" Then Exit Sub ' Exit if null input or cancel ' Incorrect password If Password <> myPassword Then MsgBox Title:="Warning", prompt:="Incorrect Password" Exit Sub End If On Error Resume Next ' Correct password If Worksheets("Sheet2").Visible = True Then ' Check if one is visable. ' The sheet above is visiable so hide all required Worksheets("Sheet2").Visible = xlSheetVeryHidden Worksheets("Sheet3").Visible = xlSheetVeryHidden Else ' Sheets must be hidden, so unhide all required Worksheets("Sheet2").Visible = True Worksheets("Sheet3").Visible = True End If End Sub Private Sub Auto_Close() ' This macro hides sheets when the user closes the workbook ' It is a "Private Sub" to prevent it being listed with outher macros On Error Resume Next ' When workbook is closed hide sheets required Worksheets("Sheet2").Visible = xlSheetVeryHidden Worksheets("Sheet3").Visible = xlSheetVeryHidden End Sub
How secure is it?
Without password protecting the VBA Project it is not very secure as any user that knows their way around Excel will soon go into the VBA editor and unhide the sheet, but the sheets are probably safe from casual users. If the VBA project is password protected it will be more secure, but how secure will depend on the version of Excel you are using and even doing so does not encrypt the sheet so it may still be visible on some other spreadsheet software. I would not trust it with any really important data.
If you want to password protect the VBA project, then this is how you can do it
1. Go to the VBA editor
2. Right click on the VBAProject name in the Project box and select VBAProject Properties…
3. In the Properties dialog box select the Protection tab
4. Tick Lock project for viewing