MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel is Revealing Protected Very Hidden Worksheets

March 09, 2018 - by

Excel is Revealing Protected Very Hidden Worksheets

You likely know that there is a way to hide worksheets. But did you know that Excel can Very Hide worksheets? This is a trick used by the super-Exceller for years. But now, Microsoft is calling us out when we very-hide something with VBA.

To hide a worksheet, you select Home, Format, Hide & Unhide, Hide Sheet.

Hide a worksheet
Hide a worksheet

But I don't like hiding a worksheet, because anyone who is curious can go to the same menu and see that there are worksheets to unhide.

Easy to unhide
Easy to unhide

If you've never heard of a hidden sheet, here are the obscure steps for it.

  1. Press Alt + F11 to open the VBA editor
  2. In VBA, press Ctrl + R to display the Project Explorer
  3. In VBA, press F4 to display the Properties Window
  4. Use the treeview (plus signs) in the Project Explorer to find & expand the current workbook. Select the entry for the sheet that you want to hide.
  5. Look in the Properties window. Open the drop down for Visible. There are choices for xlSheetVisible, xlSheetHidden, xlSheetVeryHidden. Choose xlSheetVeryHidden to very hide the worksheet.
Choose xlsheetveryhidden
Choose xlsheetveryhidden

Caution

It is confusing what happens in VBA after you choose Very Hidden. Because the sheet you just changed is now Very Hidden, that sheet become unselected in the Project Explorer. The previous sheet becomes selected. So, your eyes see you choose xlVeryHidden, but when the drop down menu closes, it appears as if the entry changed to xlSheetVisible. It did not. That is the default answer for the previous worksheet.

Very hidden sheets are awesome because prying eyes won't know that they are there using the menu system in Excel.

Nothing to unhide
Nothing to unhide

The five steps above are tedious. It would be easier to add a short macro to your Personal Macro Workbook: 

Sub VeryHideThisSheet()
	ActiveSheet.Visible = xlVeryHidden
End Sub

But here is the buggy behavior. If you very hide a worksheet that is also protected, the existence of the workbook will be called out by the File, Info box:

Payroll sheet is supposed to be very hidden
Payroll sheet is supposed to be very hidden

The workaround is to not protect the very hidden worksheet. You would have to protect the workbook to prevent anyone from unhiding the worksheet. Or, you could have a macro system that would (a) Very Hide and Unprotect or (b) Unhide and Protect.

Every Friday, I examine a bug or other fishy behavior in Excel.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Don't wrap Sum() around all your calculations."

Title Photo: Flash Buddy / Pixabay