Have you ever been frustrated because you cannot find the sheet an Excel calculation is referring to? It’s not a variable listed in the name manager, it’s not hidden in the normal manner, so where on earth could it be! I give you Excel’s “very hidden” worksheets. Sneaky, no?
In actual fact a worksheet can be visible, hidden or very hidden. Before we look at very hidden sheets, though, let’s go over the basics.
Hiding / Unhiding a sheet – the basics
Hiding a sheet from view is actually very easy. Simply right click on the tab and choose the “Hide” option.
To make the sheet visible again, click on any of the remaining tabs and click on “Unhide…” Of course, if no tabs are hidden this option will be greyed out (unavailable). It will also appear greyed out if you have set a sheet / sheets to be very hidden, making it more difficult for a user to access or edit important calculations for example on this very hidden sheet.
Very hidden sheets
To make a worksheet very hidden or to make it visible again (in Excel 2010), you will need to access the Visual Basic Editor via the developers tab on the ribbon. First if you haven’t got the developer tab visible on your ribbon, you will need to make it visible. Step 1 of this previous article shows you how to do this.
Once the developer tab is visible, click on the “Visual Basic” button.
This will open the Visual Basic Editor.
If the “Properties window” is not already open, click on “View” > “Properties Window” or press the function key F4.
In the project window above the properties window, click on the sheet you want to make “Very Hidden”. In my example, I have chosen sheet 2 as shown in the picture above. The very last property of the sheet, is the visible property. There are three possible options. Click once on the value of the Visible property to see the drop down arrow to access the different options.
To make the sheet “Very Hidden”, choose the last option “2 – xlSheetVeryHidden”. To make a very hidden sheet visible again, choose “-1 – xlSheetVisible”. The second option “0 – xlSheetHidden” simply hides the sheet. You can make it visible again by right clicking on any of the excel tabs and choosing “Unhide…” as shown earlier.
Remember: when a sheet is very hidden you cannot see it except in the Visual Basic Editor. A user of the sheet cannot right click a tab and see it listed among the other sheets that are simply hidden.
Using vba to make a sheet very hidden / visible
If you are familiar with vba, you can of course use the following code to change the Visible property of a sheet instead.
To make a sheet very hidden:
Sheets("SheetName").Visible = xlVeryHidden"
To make a sheet hidden:
Sheets("SheetName").Visible = xlHidden"
To make a sheet visible:
Sheets("SheetName").Visible = True"
Hope you find this useful!