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:

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:

Hidden sheets will be unhidden only when users click on the “View Worksheets” button, the code is also simple:

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 :

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:

Depending on your choices made when protecting the worksheet, this line may look different:

If you found this surprising, then what do you think of the following code?

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…

Leave a Reply

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