Hiding Excel sheets is very useful to protect content, either
for privacy/security reasons or for pure precaution - avoiding users
messing, even involuntary, with complex data like lookup tables. But
if you really want to protect the hidden content, you
need to prevent users from unhiding it.Regularly
hidding/unhidding excel sheets - Using the trick EVERYBODY knows
There is a regular way to hide Excel sheets. It is very simple
but the problem with it is that almost everyone knows how to unhide
them - It is also easy. And, if they don't know, all they need to do
is to search Excel help for "unhide sheet". To hide a Sheet, all you
need to do is:
1) Select the Sheet you want to hide (Sheet2):
2) Select Sheets on the Format menu
and click Hide:
3) The selected Sheet (Sheet2) is hidden:
But anyone can unhide it again:
1) Select Sheets on the Format menu
and click Unhide:
2) Select the sheet to unhide (Sheet2) from the list on the
dialog box, then press OK:
3) The selected Sheet (Sheet2) is unhidden:
New Privacy Level 1 - Using the trick nobody knows
There is, however, a trick that almost nobody knows
because it demands utilization of an Excel feature most people don't
know how to use, even if they know it exists - The Macro editor. To
use it to hide an Excel sheet, you need to do this:
1) Open the Macro editor pressing the keys ALT + F11:
2) Select the sheet you want to hide (picture: Sheet2):
3) Set the visible property to 2 - xlSheetVeryHidden.
Don't set the property to 0 - xlSheetHidden,
as it will be the same as hiding it from the Format
menu!
4) Save the changes. The sheet is no longer visible:
New Privacy Level 2 - Password protecting the
trick nobody knows
Level 1 privacy will work for most people because they will not
know how to unhide the sheet. They will try the traditional way but
nothing will appear on the unhide box so they will be lost. But if
they search for help, on the web as a instance, they may find how to
unhide it. In fact, they may find this very article on a search
engine!
There is a solution for this - Set a password for the macro
editor. users will be prompted for the password before
they can access the sheet properties, so they cannot
change them without either knowing the password or braking it
- an hacker, not user, task.
To set a password for the macro editor, do this:
1) On the Macro editor, select VBAProject Properties
from the Tools menu:
2) Select Protection on the dialog box and
activate the "Lock Project for Viewing" box. Enter
(twice) the chosen password. Press OK and save the file:
Unhidden it again
Is there any way of unhidden the sheet after this?
Of course, doing this:
-
Level 1 - Enter the Macro editor and change the
visible property again for the default status -1 -
xlSheetVisible. Save it again and the sheet will be
visible.
-
Level 2 - Enter the Macro Editor, insert the
password and change the visible property again for the default
status -1 - xlSheetVisible. Save it again and the
sheet will be visible!
It is also possible to delete the macro editor password:
- Uncheck the "Lock Project for Viewing" box;
- Enter a blank password replacing the original one (repeat Level 2
steps with a blank password).
Can I know more about this subject ?
Check our FREE PC Tips and Tutorials.
They will be
regularly published. Better still,
subscribe our
Email Newsletter. You will be informed when contents updates are
made and automatically granted participation on any future
promotion.
|