Hack 5 Hide Worksheets So That They Cannot Be Unhidden

   

Sometimes you want a place for information that users can't read or modify. Build a backstage into your workbook, a place to keep data, formulas, and other minutiae consumed by, but not seen in, your sheets .

A useful practice when setting up a new Excel workbook is to reserve one worksheet for storing information users do not need to see: formula calculations, data validation, lists, useful variables and special values, sensitive data, and so forth. Although you can hide the sheet by selecting Format Sheet Hide..., it's a good idea to ensure that users can't unhide it by selecting Format Sheet Unhide....

You can, of course, simply protect the worksheet. However, this still leaves it in full viewsensitive data, scary formulas, and all. Also, you can't protect a cell linked into any of the controls available to you from the Forms toolbar.

Instead, we'll fiddle with the worksheet's Visible property, making it xlVeryHidden . From the VBE (Tools Macro Visual Basic Editor or Alt/Option-F11), make sure the Project Explorer window is visible by selecting View Project Explorer. Find the name of your workbook within the Project Explorer and expand its hierarchy by clicking the + to the left of the workbook's name . Expand the Microsoft Excel Objects folder within to reveal all your workbook's worksheets.

Select the sheet you want to hide from the Project Explorer and reveal its properties by selecting View Properties Window (or by pressing F4). Make sure the Alphabetic tab is selected, and look for the Visible property at the very bottom. Click the value box on the right associated with the Visible property and select the last option, 2 - xlSheetVeryHidden , as shown in Figure 1-9. Press Alt/ -Q to save your changes and return to Excel. The sheet will no longer be visible via the Excel interface and won't appear as a choice under Format Sheet Unhide....

Once you have selected 2 - xlSheetVeryHidden from the Properties window, it might appear as though your selection had no effect. This visual bug sometimes occurs and shouldn't concern you; if the sheet no longer appears in the Format Sheet Unhide... choices, you know it had the desired effect.

Figure 1-9. Properties window of a worksheet having its visible property set to 2 - xlSheetVeryHidden

To reverse the process, simply follow the preceding steps, this time selecting - 1 - xlSheetVisible .