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.

Right clicking on the sheets tab does not allow these sheets to be unhidden

Right clicking on the sheets tab does not allow these sheets to be unhidden

Very hidden sheets can only be made visible through the VBA editor.

Setting in the VBA editor to set the visibility of a sheet

Select a sheet from the Project window and then view or change the Visible setting in the Properties window

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.

Doing it

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.

The code

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…
Properties menu
3. In the Properties dialog box select the Protection tab
4. Tick Lock project for viewing

Protection settings
5. Complete the password fields and click OK

Advertisements

Posted on June 30, 2012, in Encryption, Excel, Word & VBA and tagged , , , . Bookmark the permalink. 2 Comments.

  1. SpreadSheetNinja

    The users that knows about vba pretty much knows you can bruteforce check passwords by running random letters from A to Z for as many letters in a string it takes to crack the code, usualy takes 1-10 min to create the code (easy difficulty) then about 1-60 Seconds to crack any code in Excel 2010… so to anyone programing in Excel, just dont save anything valuable or important on any sheet, if theres even the slightest hint of a sheet.. data can be retrived regardless of password.. not to mention the password can be changed whiout knowing the originale whit other tricks.. so yea…

    So if one is thinking about Excel or Office program Security in general, if a user knows how to find very hidden sheets, then they will get the data they want, so i only use very hidden, or and passworded \ locked sheets for preventing accidental overwriteing of formulas that are important… and NOT to prevent users from meddeling whit my code or and stealing as there is no point.. users who cant crack the password code of Excel probebly wont be able to use my code anyway cus they wont understand it… so yea think everyone gets the point.

    Excel Security = Minor inconvinience and a good way to just prevent End user accidental input.
    And invested time in trying to secure a sheet = Time wasted or lost.

    • Hi SpreadSueetninja, I agree. I only use this for spreadsheets that only a few coworkers have access to and I hide sheets that I prefer them not to play with. If they had the skills to work out how to access the sheets I wouldn’t be worried about them to accessing them as I expect they would have enough skill to know what any changes they may make will do.

%d bloggers like this: