The PC HelpSite
www.officehelp.biz
Office Tutorials & Software Solutions
Specializing in Advanced Spreadsheet & Office Macro Solutions.
SUPERHIDE Microsoft® Excel sheets

Application Microsoft Excel Version 1.00
Author OfficeHelp
Tested on versions: 2000 (9.0), XP (10.0), 2003 (11.0), 2007 (12.0), 2010 (14.0), 2013, 2016 - should work on any version from Office 97


Hiding Excel sheets is useful: you may want to remove complex contents users shouldn’t change, like lookup tables, or just want to protect your intellectual property and deny end-users the view of data that, being necessary for calculation, you don’t want disclosed. Anyone knows how to regularly hide Excel sheets – meaning anyone knows how to UNHIDE them. What if you could hide Excel sheets so well that it would take a hacker to unhide it?


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):

Select sheet to hide

2) Select Sheets on the Format menu and click Hide:

Hide it from the Format menu

3) The selected Sheet (Sheet2) is hidden:

The sheet is hidden

But anyone can unhide it again:

1) Select Sheets on the Format menu and click Unhide:

Unhide it from the Format menu

2) Select the sheet to unhide (Sheet2) from the list on the dialog box, then press OK:

Select the hidden sheet to unhidde

3) The selected Sheet (Sheet2) is unhidden:

The sheet 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:

Open the Macro Editor

2) Select the sheet you want to hide (picture: Sheet2):

Select sheet to hide

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!

Set the visible property

4) Save the changes. The sheet is no longer visible:

The sheet is hidden

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:

Open the Macro Editor

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:

Select sheet to hide

 

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.

Get FREE PC TIPS every month:

 


www.OfficeHelp.Biz

(c) 2004 - 2024 OfficeHelp.Biz