Excel Hacks Ebook [Electronic resources] نسخه متنی

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.