Excel First

Excel Solutions

Hide Excel Sheets if Macros are not Enabled

Scenario:

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…

Enable Content

Enable Content

 

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:

Wellcome Screen

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:

After Enabling Macros

After Enabling Macros

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:

Sheet3.xml

Workbook.xml

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.

Bottom Line:

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…

3 Comments to Hide Excel Sheets if Macros are not Enabled

  1. abss123 says:

    I have adopted that approach to my xlsm file.
    I have hidden my worksheets with setting xlsveryhidden. At first i have an info sheet advising users to enable content to unhide my worksheets.
    I have sent my xlsm template to other users to work with their pcs.
    However when they open xlsm workbook they get the
    enable content prompt on top but my worksheets are not hidden.
    They have same excel 2016 version as i do.

  2. abss123 says:

    This approach works successfully in my pc though.

Leave a Reply

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

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: