There are some things you can do in the VBA Editor that won’t trigger the dreaded warning about opening a file with a macro. Hiding a worksheet so nobody can find it is one of those things.
Excel worksheets have a visible property that can take on one of three values.
By default all worksheets are visible. Meaning the visible property is true, or set to xlSheetVisible. When you hide a worksheet, Excel changes the visible property to xlSheetHidden.
Hiding a worksheet is simple, just right click on the worksheet tab and select Hide from the pop-up menu.
However, anybody looking for a hidden worksheet can find it easily by right clicking on any worksheet and click Unhide from the pop-up menu.
Note: If the Unhide button is grayed out there are no sheets hidden by using this method.
Make a Worksheet Disappear
If you really want to keep a worksheet secret from most people, set the visible property of a worksheet to xlSheetVeryHidden.
Here’s how you make a worksheet disappear:
- Use the keyboard shortcut Alt+F11 to bring up the VBA Editor.
- Go to the View menu and select Project Explorer to make sure it’s visible (Ctrl+R shortcut)
- Go to the View menu and select Properties Window to make sure it’s visible (F4 shortcut)
- In the Project Explorer window click on the worksheet you want hidden
- In the Properties Window select the Visible property
- Click the Visible property drop-down menu and select 2 – xlSheetVeryHidden
- Close and return to Excel by using the keyboard shortcut Alt+Q
Now MySecretWorksheet doesn’t show in the Unhide dialog box, only the MyHiddenWorksheet shows, which was hidden in the normal manner.
If no other worksheets are hidden, then the pop-up box shows a grayed out Unhide option.
The only place to find a VeryHidden worksheet is in the VBA Editor, which most people wouldn’t think to look. And if you really want to keep them out, you can set a password to keep them from opening the VBA Editor.
Bonus Tip – Use VBA to Change the Sheet Visible Property
You can change the visible property of a worksheet from the Immediate window in the VBA Editor by using the (Name) property of the sheet.
- Open the VBA Editor (Alt+F11)
- Show the Immediate window (Ctrl+G)
- Type the sheet (Name) followed by a period and visible, a space, then equals sign (=) and select xlSheetVeryHidden from the drop-down list, and hit enter.
For example type: sheet3.visible = xlSheetVeryHidden and hit enter.
The picture below depicts how this is done.
You’ll notice in the Properties window that Sheet2 has a Name property and a (Name) property. The (Name) property can only be changed in the VBA Editor Properties window.