Hello, Excellers welcome back to another #Excel tip in my #MacroMonday Series. Have you ever received an Excel workbook and don’t know if there are hidden sheets in it?. Not the just ‘Hidden’ sheets but ‘Very Hidden’ sheets? Well, here is a really simple Excel VBA macro that you can easily run. It will display what worksheets have been set with a status of ‘Very Hidden’.

Hidden vs Very Hidden Excel Worksheets.

Before we write our macro let’s take a quick look at the difference between Hidden Worksheets and Very Hidden worksheets. Excel has two levels of hidden worksheets. The regular ‘Hidden’ worksheet is accessible and changed by the Excel user from the worksheet itself.

• Right Click on the worksheet you want to hide
• Select Hide

In order to reverse this action and make the worksheet visible again just right-click. Simple select ‘Unhide’ from the pop-up menu.

So, onto the difference with a ‘Very hidden’ worksheet. So, if one of my worksheets is flagged or set to Very hidden, then it will not appear in the list of the worksheets that can be selected to become visible again. For example, if I flag the sheet named Summary in Excel workbook as Hidden, and the worksheet named Data as Very hidden. The worksheet Data will not be even available to unhide no matter how many times I try.

In order to use the Very Hidden status for Excel worksheets, we need to open the VB Editor and change the status of the worksheet or sheet in there.

Use the VB Editor- Worksheets Very Hidden

It is easy to flag worksheets as Very hidden as long as you have access to the VB Editor. You will need to open the Visual Basic Editor.  There are two ways to do this.  Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result. 

This now allows you to see the worksheets that are in your Excel workbook. In order to change the status of a worksheet to very hidden, follow the simple steps below.

• Select the worksheet to hide.
• In the properties window of the VB editor scroll to Visible
• Select 2 – xlSheetVeryHidden

Once this property has been changed, it will not appear in the right-click menu or use the Home Tab | Cells Group | Format | Visibility | Hide & Unhide | Hide Sheet. Only of a user is familiar with sing the VB method will they realize that sheets have been set to Very Hidden. So, now we know how we have covered the differences between Hidden and very Hidden. We can now write a simple macro which will insert a new worksheet into your workbook and list any Very Hidden worksheet.

This is very useful if you receive a new worksheet and you are unsure if you are dealing with Very Hidden worksheets. Run this macro and Excel will list them for you. Saves time not going into the VB Editor.

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor.  There are two ways to do this.  Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.  If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook.

This macro will be useful in the future so I am going to save it in my Personal Macro Workbook.

More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

We need to start off the macro by inserting a New Module.  Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.  In this example, I have called it simply ListHiddenSheets.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]
Sub ListHiddenSheets
End Sub
[/stextbox]

Declaring Variables.

We need to declare two variables in the macro. This just means we get Excel to create a memory container, or get it to allocate memory to store these values. The two variables we are declaring are ws as Worksheet and x as Integer. We are also setting x as the value of 1.

[stextbox id=’info’]
Dim ws As Worksheet
Dim x as Integer
x= 1
[/stextbox]

Insert A New Worksheet.

We first insert a new worksheet at the beginning or before any other worksheets in the workbook. I like to place it here as it is like an index or contents page of the workbook. You can choose to place it after all worksheets by changing the Worksheets.Add(Before to Worksheets.Add(After in the VB code.

[stextbox id=’info’]
Worksheets.Add(Before:=Worksheets(1)).Name = “HiddenWorksheets”
[/stextbox]

Use The For Next Loop.

We use the For Next Loop method to loop through each of the worksheets in the Excel workbook. If the worksheet visible property is flagged as Very Hidden then this macro will enter the name of the worksheet into the newly created ‘HiddenWorksheets’. The worksheet name is placed in the cell reference of Cell(x, 1). This refers to Row 1 to begin as we have declared that x = 1, and Column 1. Every time excel loops through each worksheet the Row value of 1 increases by 1. Subsequently, each worksheet name is placed in the next row cell down from the previous one.

[stextbox id=’info’]
For Each ws In Worksheets
If ws.Visible = xlSheetVeryHidden Then
Sheets(“HiddenWorksheets”).Cells(x, 1) = ws.Name
x = x + 1
End If
Next ws
[/stextbox]

Ending The Macro.

The routine then ends with the End Sub piece of code.  This was already entered into the module for us when started the type the name of the Macro.

[stextbox id=’info’]
End Sub
[/stextbox]

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

How To Excel At Excel – Macro Mondays Blog Posts.

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.