Very hidden Excel Sheets

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.

right click hide

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.

Visual Basic

This will open the Visual Basic Editor.

Visual Basic Editor

If the “Properties window” is not already open, click on “View” > “Properties Window” or press the function key F4.

view properties windowThis will open up the window as shown below:

properties windowIn 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.

three 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!

+Alesandra Blakeston

2 thoughts on “Very hidden Excel Sheets

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s