Thursday, April 3, 2014

Very Hidden Sheets


I suppose we all know how to hide and unhide a sheet. If you have a sheet that you wish to keep secret when the file is sent across to people, you will hide that sheet. But receiver of the file can unhide this sheet and view the contents. You can password protect the structure so that users cannot unhide the sheets.

There is one more option that is very little known to Excel front end users. This option comes somewhere in between hiding a sheet and password protecting the book. We are talking about one of the VBA property of sheet i.e. very hidden.

If You Don’t Have Developer Tab Enabled
  1. Open up the VBE (you can find all about how to access VBE window from here)
  2. In the Project Explorer window, click on the sheet you want to hide
  3. In the Properties window, click on Visible, you can see a dropdown next to that field. Click on that dropdown button and you can see 3 options in it. Select third option which is 2 – xlSheetVeryHidden.
  4. Go back to Excel and you will observe that the sheet is gone now. If you notice the hidden sheets list (by right clicking on any sheet tab and click unhide), you will not see this sheet.
Normal users will not be able to notice this sheet. But anyone can repeat the above steps and select 1 – xlSheetVisible to make the sheet visible. Selecting 0 – xlSheetHidden will hide the sheet, just like how you do normal hiding.


If You Have Developer Tab Enabled

In Excel 2007 and later, you can enable the Developer tab and view sheet object properties in Excel itself rather than in VBE. See here how to enable Developer tab.

Under the Developer tab, under Controls group, click on Properties. You will get the Properties window, just the one you see in VBE. Repeat step 3 & 4 above to make the sheet very hidden.