How to Hide and Unhide Sheets in Excel with VBA (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

This article will show you how to hide and unhide sheets in Excel with VBA. Hiding and unhiding in Excel can help the user to organize data more effectively. You can protect the workbook from unwanted editing. You can also hide sensitive data for safety. In this article, we will show you the most common ways of hiding and unhiding sheets in Excel with VBA.


How to Hide and Unhide Sheets in Excel with VBA: 6 Suitable Examples

There are many ways to hide and unhide sheets in Excel with VBA. Out of those many ways, we will show you six convenient methods. Each method comprises practical examples.


1. Hide and Unhide Single Sheet

Let’s start with the basics of hiding and unhiding in Excel VBA. We have a workbook that has five sheets, each containing an imaginary data table of “Records of Elementary School”. Now, we want to hide every single one of them. Then we will unhide the sheet again.

Dataset to be used for how to hide and unhide sheets in excel with vba

To hide the “Elementary” sheet, copy the following code in a new Module and press the Run button.

Sub Hide_Single_sheet()
  Sheets("Elementary").Visible = xlSheetHidden
End Sub

You can also use any of these lines to get the same output.

Worksheets("Elementary").Visible = False

You can use a False statement instead of xlSheetHidden.

Sheets(1).Visible = False

Here, Sheets(1) takes the first sheet in the workbook.

VBA Code to hide a single sheet.

This one line of code calls the “Elementary” sheet with the VBA Sheets function. Then, from the Visible properties, it selects the xlSheetHidden option.

After running the code, you can see we have hidden the “Elementary” sheet.

Output after hiding the Elementary sheet

Now, let’s unhide the “Elementary” sheet. We will use almost the same code. Just replace xlSheetHidden with xlSheetVisible.

So, use the following code to unhide the “Elementary” sheet.

Sub Unhide_Single_Sheets()
    Sheets("Elementary").Visible = xlSheetVisible
End Sub

You can also use any of the following lines.

Worksheets("Sheet1").Visible = True

You can use the True statement instead of xlSheetVisible.

Sheets(1).Visible = True

VBA Code to Unhide single sheet.

And, as you can see, we have the “Elementary” sheet back.

Output after the Elementary sheet is restored.


2. Using xlSheetVeryHidden Property and Way to Unhide

This is a more useful way to hide any sheet. In this case, the viewer will not see the hidden sheets. So, this method is more effective.

For this method, we are going to use the previously used dataset.

Copy the following code into a new Module and click on the Run button.

Sub Sheet_Very_Hidden()
    Worksheets("Elementary").Visible = xlSheetVeryHidden
End Sub

VBA Code for very hidden sheets.

Doing so, you can see the “Elementary” sheet is invisible. And you cannot unhide the sheet manually.

Dataset to be used for very hidden sheets.

To unhide the sheet, you can follow the same basic procedure as previously shown.

You can use the following code.

Sub Unhide_Very_Hidden()
Sheets("Elementary").Visible = True
End Sub

VBA Code to Unhide very hidden sheet.

If you run the code, the “Elementary” sheet will be visible.

Showing result after the very hidden sheet is restored.


3. Use of Array to Hide and Unhide Multiple Sheets

In this method, we will hide and unhide multiple sheets with a VBA array. We will take the sheet names in an array. Then we will use a For Loop to hide them.

We are using the same dataset. We want to hide three sheets, i.e. “Elementary”, “High School” and “College”.

Copy the following code into a new Module and run it to hide multiple sheets using the following VBA code.

Sub Hide_Multiple_sheet_with_Array()
 Sheets_Arr = Array("Elementary", "High School", "College") 
    For i = LBound(Sheets_Arr) To UBound(Sheets_Arr)
     Sheets(Sheets_Arr(i)).Visible = False
    Next i
End Sub

VBA Code to hide multiple sheets with Array.

After running the code, all those sheets have been hidden.

Showing output after hiding multiple sheets with Array.

To unhide those sheets, you can use almost the same code. You just have to replace the False statement with True in the Visible properties.

Use the following code to restore those sheets.

Sub Unhide_Multiple_sheet_with_Array()
 Sheets_Arr = Array("Elementary", "High School", "College")
    For i = LBound(Sheets_Arr) To UBound(Sheets_Arr)
     Sheets(Sheets_Arr(i)).Visible = True
    Next i
End Sub

VBA Code to Unhide multiple sheets with Array.

After running the code, you can see all those hidden sheets have appeared.

Showing output after unhiding multiple sheets with Array.


4. Hide and Unhide Sheets by Tab Color

We can also hide and unhide sheets with tab colors. This procedure is also useful as you can hide and unhide a large number of sheets very quickly. You only have to color the tabs properly, and then with VBA code, you can hide and unhide them quickly.

We changed the tab color of the “Middle School” and “College” sheets to blue.

Dataset to be used for hiding and unhiding by tab color.

Right now, we want to hide and unhide these two sheets.

Copy the following code into a new Module and press the Run button.

Sub Hide_Blue_Colored_Sheets()
 Dim ws As Worksheet
 Dim Tab_Color As Long
  Tab_Color = 12611584
 For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = Tab_Color Then
      ws.Visible = False
    End If
 Next ws
End Sub

This code checks the tab color of every worksheet to see if it is blue. Upon meeting the criteria, the code hides the corresponding sheet. The number 12611584 is the blue color code in VBA.

VBA Code to hide sheet by color tab

After running the code, you can see that those Blue colored tabs are not visible anymore.

Showing result after the colored tabs are hidden.

Now, to unhide those two sheets, we will almost use the same. We only have to change the Visible properties to True.

Use the following code to unhide those sheets.

Sub Unhide_Blue_Colored_Sheets()
Dim ws As Worksheet
 Dim Tab_Color As Long
   Tab_Color = 12611584
  For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = Tab_Color Then
     ws.Visible = True
    End If
  Next ws
 End Sub

VBA Code to unhide colored tabs.

After running the code, we can see those two colored sheets are visible now.

Showing output after all colored tabs are restored.


5. Hide and Unhide a Specific Worksheet Based on Cell Value in Another Sheet

Now let’s see how we can hide and unhide worksheets based on cell values in another sheet.

We have these two sheets of “Sales” and “Summary”. In the “Sales” sheet there is a Hide/Unhide box. We will write Hide/Unhide in D15, and the “Summary” sheet will be hidden.

We have to use an Event Handler to perform this procedure. So, we can’t insert the code in any Module. We have to insert the code in the “Sales” sheet.

Dataset for hiding one sheet based on the cell value of another sheet.

Write the following code in the “Sales” sheet in VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
 If [D15] = "Hide" Then
  Sheets("Summary").Visible = False
  Else
  Sheets("Summary").Visible = True
 End If
End Sub

🔎 VBA Code Breakdown:

This code uses Event Handler. If you write “Hide” in D15 of the “Sales” sheet, the “Summary” sheet will be hidden; otherwise, the “Summary” sheet will remain visible.

VBA Code to hide a sheet based on another cell value of another sheet.

Now, go to the “Sales” sheet and write “Hide” in the D15 cell.

And, as you can see, the “Summary” sheet is no longer visible.

Result output after hiding the target sheet.

To unhide the sheet, you can use a similar VBA code. We have to keep these two codes in the same VBA sheet.

Copy the following code into the same “Sales” sheet.

Private Sub Unhide_Worksheet_Change(ByVal Target As Range)
 If [D15] = "Unhide" Then
   Sheets("Summary").Visible = True
  Else
   Sheets("Summary").Visible = False
 End If
End Sub

VBA code to Unhide specific sheet.

Go to the “Sales” sheet and write “Unhide” in D15 and press Enter.

And, the “Summary” sheet is restored.

Showing output after unhiding the sheet.

Note: There are two codes in this procedure, and both are inserted in the same VBA sheet. Make sure one of the Private Sub is named as Worksheet_Change. You should give a different name to the other Private Sub.

6. Hide and Unhide Sheets in Protected Workbook

If your workbook is unprotected, you need to protect it first.

You can use the following code to unprotect the workbook.

Sub Unprotect_Worksheet()
  ActiveWorkbook.Unprotect
 End Sub

Showing output after unhiding the sheet.

If your workbook is password protected, then you should use this code.

 Sub Unprotect_Worksheet()
 ThisWorkbook.Unprotect "password"
 End Sub

VBA Code to unprotect using password.

Now, you can hide and unhide using any of the previous methods.


How to Unhide All Hidden Sheets with Excel VBA

In this section, we will learn how to unhide all sheets with VBA. In this case, we have already hidden some sheets. We will use a VBA code to unhide them.

Dataset with hidden sheets.

Write the following code in a new Module and press the Run button.

Sub Unhide_All_Sheets()
 Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
   ws.Visible = True
 Next ws
End Sub

VBA code to unhide all hidden sheet.

After running the code, you can see all the sheets are visible now.

Showing output after unhiding all hidden sheets.


How to Hide All Sheets Except for Active Sheet

We can hide all sheets except the Active Sheet.

In this case, we have taken the “Elementary” sheet as the Active Sheet.

Write the following code in a new Module and click on the Run button.

Sub Hide_All_but_Activesheet()
 Dim ws As Worksheet
   For Each ws In ThisWorkbook.Sheets
    If ActiveSheet.Name <> ws.Name Then
     ws.Visible = False
    End If
   Next ws
End Sub 

This code checks every worksheet in the workbook. If the worksheet name doesn’t match the Active Sheet, the worksheet will be hidden.

VBA Code to hide all sheets except the Active Sheet.

After running the code, you can see all the other sheets, but the active one has been hidden.

Showing all sheets are hidden except the Active Sheet.


How to Hide All Sheets Except for One Sheet

We can also keep some specific sheets visible and hide all other sheets.

We want to hide only the “Elementary” sheet. Let’s follow the instructions below to hide all sheets except one in Excel with VBA.

For this, use the following Code.

Sub Hide_All_Sheets_Except_One()
 Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Elementary" Then
     ws.Visible = False
    End If
  Next ws
End Sub

VBA code to hide all sheets except for a specific sheet.

After running the code, we can see all other sheets have been hidden except for the “Elementary” sheet one.

Shows all sheets are hidden except for a specific one.


Frequently Asked Questions

  • How do we unhide all sheets?

Click Right on the Sheet tab at the bottom, and select the “Unhide” option.

Then, in the Unhide dialog box, – Press the Ctrl key and click the sheets you want to show, or press the Shift + Up/Down Arrow keys to select multiple (or all) worksheets, and then press OK.

  • How do I Automatically Unhide Sheets in Excel?

Click Right on a sheet tab, and then click Select All Sheets on the shortcut menu. On the Home tab, click Format > Visibility > Hide & Unhide > Hide Sheet.


Things to Remember

  • Make sure to call the sheet’s name properly. Check if there are any spaces or special characters in the sheet name. Also, you should be careful with capital letters.
  • In the case of a protected sheet, you cannot hide or unhide any sheet without unprotecting it in the first place.
  • If you try to hide a hidden sheet or unhide a visible sheet, the code could give you an error. For that, you can use Error Handler.
  • It’s a good practice not to hide the Active Sheet. Doing so can give you unexpected results.
  • Remember, you can’t hide all the sheets. At least one sheet always has to remain unhidden.

Download Practice Workbook

You can download and practice this workbook.


Conclusion

So, we have shown you how to hide and unhide sheets in Excel with VBA. We also discussed when to use all those methods. We hope you find the content of this article useful. If there are further queries or suggestions, you can mention them in the comment section.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo