If you want to make sure a hidden worksheet stays hidden, don't rely on the easily reversed Hide command. For better security, try this little VBA trick instead.
You can hide an Excel sheet, but users can unhide it just as quickly. If you want a more securely hidden sheet, hide it in the Visual Basic Editor (VBE).
SEE: Download: Build your Excel skills with these 10 power tips (TechRepublic)
Fortunately, the technique is simple for you — and users won't suspect a thing. First, let's look at the traditional method for hiding a sheet:
- Open a new workbook.
- Click Sheet2's tab to select it.
- From the Format menu, choose Sheet.
- From the resulting submenu, choose Hide. Excel hides Sheeet2 and its tab.
To unhide Sheet2, you'd choose Sheet from the Format menu and select Unhide. Then, you'd select Sheet2 in the Unhide dialog box and click OK. (For now, don't unhide Sheet2.) Even users with just a little experience might find this dialog box and unhide a sheet.
Excel has a more secure setting known as "very hidden." A very hidden worksheet can't be unhidden using the Excel user interface because it doesn't appear in the Unhide dialog box. The average user won't even know the sheet exists, let alone know how to unhide it. You'll need the VBE to apply this property as follows:
- Switch to Excel's VBE (press [Alt]+[F11]).
- In the Project Explorer, select the sheet you want to hide. This time, hide Sheet3.
- In the Properties window, select 2 - xlSheetVeryHidden from the Visible property's drop-down list. When you do, Excel applies the property and then selects the first sheet, Sheet1, which is a bit distracting. That behavior is normal, so don't let it bother you.
When you return to Excel, Sheet3 isn't visible. Nor does Excel list Sheet3 in the Unhide dialog box. In fact, Excel dims the Unhide command if the only sheets hidden are very hidden sheets. Sheet2 is visible because (earlier) we used the Hide command from the Format menu to hide it.
The result is a very stealthy sheet, but it's still vulnerable to savvy users who know the technique. To unhide Sheet3, return to the VBE and choose -1 - xlSheetVisible. (That's a negative 1, which denotes the constant's value.)