Very Hidden Sheet in Excel

In Excel, there can be two types of hidden Worksheets.

One is ‘hidden‘ and other one is ‘very hidden‘.

The normal ‘hidden’ mode is known to most of the Excel users. But the ‘very hidden’ mode is less known and Advanced Excel users make use of this feature. Very Hidden Worksheets are often used to store sensitive information and intermediate formulas.

In this tutorial, we will see the difference between hidden and very hidden worksheets.

How to hide and unhide Worksheets?

To hide a Worksheet,

right-click on the corresponding Worksheet tab and select Hide from the options.

We can hide multiple Worksheets using the same method.

What we did here is, sending the Worksheet to normal ‘hidden’ mode.

Now, to Unhide the hidden Worksheet or Worksheets,

right-click on a visible sheet tab > select Unhide…

All hidden Worksheets in the Workbook will be listed in the Unhide dialog.

Select the Worksheet or Worksheets to unhide and click on OK.

What is a Very Hidden Worksheet?

There are 3 modes of visibility for an Excel Worksheet and they are,

1. Visible

2. Hidden

3. Very Hidden

Visible mode is when the user can view and access a particular Worksheet.

In both hidden modes, the Worksheet won’t be visible to the user. At the same time ‘very hidden’ mode is quite different from the normal ‘hidden’ mode.

Very Hidden Worksheets won’t be listed in the Unhide dialog and can be accessed only through the VBA interface of Excel. Unless we open the VBA Editor of Excel, we won’t be able to know whether the Workbook contains very hidden Worksheets.

To make a Worksheet Very Hidden,

go to the Developer tab of the Excel ribbon > Visual Basic

Every Worksheet present in the Workbook will be listed on the tree view on the left sidebar of the VBA Editor.

In the Properties window docked on the left side bar, click on the drop-down menu against the label Visible

There will be 3 options.

1. xlSheetVisible

2. xlSheetHidden

3. xlSheetVeryHidden

Right now the Worksheet called Marks is in visible mode (-1 – xlSheetVisible).

To make this Worksheet Very Hidden, select 2 – xlSheetVeryHidden

The Worksheet went into very hidden mode and disappeared from the sheet tabs area.

As the Worksheet is in Very Hidden mode, it won’t be listed in the Unhide dialog.

Now, to make the worksheet visible, go to the VBA Editor > select the sheet called Marks from the left side bar > go to the Properties window > select xlSheetVisible from the drop-down menu against the label Visible.

Macro to Unhide Very Hidden Worksheets

Following is the VBA code to unhide every Very Hidden Worksheet in a Workbook

Sub UnHideVeryHiddenSheets()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Visible = xlSheetVeryHidden Then
Ws.Visible = xlSheetVisible
End If
Next Ws
End Sub

Macro to send a worksheet to Very Hidden Mode

Sub VeryHiddenSheet()
Sheets("Marks").Visible = xlVeryHidden
End Sub

Macro to send selected worksheets to Very Hidden Mode

The following VBA code when executed will send every selected worksheet to Very Hidden mode.

Sub SelectedSheetsVeryHidden()
Dim Ws As Worksheet
For Each Ws In ActiveWindow.SelectedSheets
Ws.Visible = xlSheetVeryHidden
Next Ws
End Sub

---