Hide Excel Sheets if Macros are not Enabled
If your workbook contains macros designed to assist you or your users in typing data, or you have UDF’s – User Defined Functions for specific calculations, then if the users do not enable macros after opening the file, your codes are useless…
A simple way to solve this problem
is to add to your workbook a simple code that will hide the Excel sheets needed by user If the user wants to see those hidden sheets, he MUST click on Enable Macros at the Excel Message Bar, otherwise those sheets will remain hidden.
If macros are not enabled, users will see this welcome screen:
After the macros are enabled, by simply clicking on the “Enable Content” button across from the Message Bar, users will be asked to choose if they want to see the hidden worksheets:
And here are the VBA codes, located in ThisWorkbook module. Using the Workbook_Open event, if the macros are NOT enabled, the User Form where users can choose to view hidden sheets will not be displayed:
Private Sub Workbook_Open() On Error Resume Next Application.Goto Reference:=Sheets("Attention").Cells(1, "AW") On Error GoTo 0 Warning.Show End Sub
When the workbook is closed, we have to hide the worksheets again, this way the sheets that must remain hidden will be hidden at startup; if we do not hide sheets at Workbook_BeforeClose event, hidden sheets will be seen on opening, which is not desired:
Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Wsh As Worksheet Application.ScreenUpdating = False Sheets("Attention").Visible = xlSheetVisible For Each Wsh In ThisWorkbook.Worksheets If Wsh.Name <> "Attention" Then Wsh.Visible = xlSheetVeryHidden Next Application.ScreenUpdating = True On Error Resume Next Application.Goto Reference:=Sheets("Attention").Cells(1, "A") On Error GoTo 0 Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True End Sub
Hidden sheets will be unhidden only when users click on the “View Worksheets” button, the code is also simple:
Private Sub CommandButton1_Click() On Error Resume Next Application.Goto Reference:=Sheets("Attention").Cells(1, "AW") On Error GoTo 0 Dim Wsh As Worksheet Application.ScreenUpdating = False For Each Wsh In ThisWorkbook.Worksheets If Wsh.Name <> "Attention" Then Wsh.Visible = xlSheetVisible Next Sheets("Attention").Visible = xlSheetHidden Unload Me Application.ScreenUpdating = True End Sub
You can download the sample workbook, to see how it works:
Download Sample Workbook: Hide Sheets if Macros are not Enabled.xlsm
Of course, you will have to enable macros, if you want to see the hidden sheets 🙂
There is a major limitation you should be aware of!
The open xml format for workbooks, unfortunately, also allows worksheet protection to be removed. Workbook protection also can be removed… sheets that are very hidden can be unhidden even without opening the workbook, not to mention that even the Visual Basic Project protection can be easily removed.
All you have to do is to right click the workbook, choose Open With –browse for an archiver like WinRaR Archiver , or 7-Zip (any archiver will open the archive, just make sure you don’t change the default program associated to this file type, open with WinRaR only this time)
Another way to open the Excel file with an archiver is to change the .xlsm or .xlsx or whatever the Excel file extension is, to .rar or .zip. Double clicking the file will open the archive with your default archiver. (remember to change back the extension after you finish)
If you go to xl folder, you will see the workbook.xml. By simply editing the workbook.xml with any text editor, like Notepad, WordPad, Notepad++, we can unhide hidden sheets, remove workbook protection, and other changes…
For example, if sheet3 is hidden, the workbook.xml will look like in the following image:
If we simply delete the state: =”veryHidden” expression from the xml, when you open the workbook the sheet is not hidden anymore…
To remove protection from a workbook, if it’s protected, the following expression must be removed from same workbook.xml file :
<workbookProtection lockStructure = ”1”/>
If a sheet is password protected, the protection can be removed by simply editing the sheet3.xml, (found in xl folder, worksheets folder), to remove the line:
<sheetProtection password="CA49" sheet="1" objects="1" scenarios="1"/>
Depending on your choices made when protecting the worksheet, this line may look different:
<sheetProtection password="CF7A" sheet="1" formatCells="0" formatColumns="0" formatRows="0" insertColumns="0" insertRows="0" insertHyperlinks="0" deleteColumns="0" deleteRows="0" sort="0" autoFilter="0" pivotTables="0"/>
If you found this surprising, then what do you think of the following code?
Sub UnhideSheetsFromOtherWorkbook() Dim Wks As Worksheet For Each Wks In Workbooks("Hide Sheets if Macros are not Enabled.xlsm").Worksheets Wks.Visible = xlSheetVisible Next Wks End Sub
This means that you can unhide sheets from this sample workbook from any other opened workbook. It does not matter if the macros are enabled in the target file…
In conclusion, there is no real protection for Workbooks, or worksheets.
The solution presented in this post is NOT a security solution, it will simply “help honest people remain honest”. If you just intend to make sure that all users of this workbook are working with macros enabled, then you are in the right place.
Let me know what you think…