When working with large workbooks with many worksheets, it’s common to hide some worksheets to make the workbook more manageable.
With VBA, you can easily hide or unhide worksheets in bulk. This can also be useful when you want to quickly hide or unhide specific worksheets without having to find and locate them from a long list.
In this article, I will show you some simple VBA codes to hide or unhide sheets in Excel.
Sheet.Visible Property in VBA
VBA has the Sheet.Visible property (or Worksheet.Visible property) that determines whether it would be visible or hidden for the user in the workbook.
Below is the line of code that would hide the sheet named ‘Example’ in the workbook in which the code is run:
Worksheets("Example").Visible = False And here is the code that would unhide it (in case it is hidden):
Worksheets("Example").Visible = True In this example, I have used Worksheets(“Example”), but you can use any sheet name, the active sheet, or even the Sheet object stored in a variable.
You can also use xlSheetHidden instead of True and xlSheetVisible instead of False. For example, the following code would also hide the sheet named Example Worksheets(“Example”).Visible = xlSheetHidden.
Note: You need to have at least one sheet visible in the workbook. If you try to hide the last visible sheet, VBA will give you the Runtime 1004 error.
VBA Codes to Hide Sheets
Below, I have some examples of VBA codes for hiding sheets in different situations.
Hide the Active Sheet
The below code will hide the active sheet:
Sub HideActiveSheet()
' Hide the currently active sheet
ActiveSheet.Visible = False
End SubRemember that you always need to have one visible sheet in the workbook. So, if the active sheet is the only sheet in the workbook, this code will throw an error.
Also read: VBA Check IF Sheet Exists
Hide All Sheets Except the Active Sheet
Below is the code that will hide all the sheets except the active sheet.
Sub HideSheetsExceptActive()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.Visible = False
End If
Next ws
End SubThe above code uses a For Each Next loop to go through each sheet, and then it checks the name of the sheet.
If the name of the sheet is not the same as that of the active sheet, it gets hidden.
Hide Sheets by Name
If you want to hide sheets by their names, you can do that using a VBA code as shown below:
Sub HideSheetByName()
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End SubIn the above code, I specified the sheet name in double quotes and then used the Visible property of the sheet to set it to False to hide it.
In this case, I have hidden three sheets, and you can modify the code to hide more/less number of sheets.
Hide Sheets with a Specific Word in the Name
Below is the VBA code that hides only those sheets that have the word ‘Sales’ in the name:
Sub HideSheetswithSpecificWord()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If InStr(1, ws.Name, "Sales", vbTextCompare) > 0 Then
ws.Visible = False
End If
Next ws
End SubThe above VBA macro loops through each worksheet in the workbook and checks whether the name contains the word Sales or not. This is done using the VBA INSTR function.
If the word Sales appears anywhere in the name of the sheet, that sheet would be hidden.
Hide Sheets Based on Cell Value
Below, the VBA code hides any sheet that has the text “Hide” in cell A1 of the sheet.
Sub HideSheetsBasedOnCellValue()
Dim ws As Worksheet
' Loop through each worksheet and hide if cell A1 has "Hide" in it
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A1").Value = "Hide" Then
ws.Visible = False
End If
Next ws
End SubThe above code loops through each worksheet in the workbook and then checks the value in cell A1 of each sheet.
If the text in cell A1 is “Hide”, then it will hide that sheet by setting the visible property of the sheet to False.
Hide Sheets Based on Tab Color
When I’m working with a large workbook, I often give color to my worksheet tabs to group similar sheets and to make them more manageable.
This is also easier for me when I’m presenting my work on calls, as I can direct them to a specific tab by mentioning the tab color.
Below is the VBA code you can use to hide all the sheets that have a specific tab color (where you need to specify the tab color):
Sub HideSheetsBasedOnTabColor()
Dim ws As Worksheet
Dim colorCriteria As Long
' Specify the RGB color to match. Example: RGB(255, 0, 0) for red
colorCriteria = RGB(255, 0, 0)
' Loop through each worksheet, check color and hide
For Each ws In ThisWorkbook.Worksheets
If ws.Tab.Color = colorCriteria Then
ws.Visible = False
End If
Next ws
End SubIn the above code, I have specified the color criteria as RGB(255, 0, 0), which refers to the red color.
The above code then loops through each worksheet in the workbook and checks the tab color. If the existing color of the worksheet matches the criteria in our code, then that sheet gets hidden.
If you want to hide all the sheets except the one that has a specific color, you can use a code similar to what I have below here:
Sub HideSheetsBasedOnTabColor()
Dim ws As Worksheet
Dim colorCriteria As Long
' Specify the RGB color to match. Example: RGB(255, 0, 0) for red
colorCriteria = RGB(255, 0, 0)
' Loop through each worksheet, check color and hide
For Each ws In ThisWorkbook.Worksheets
If ws.Tab.Color <> colorCriteria Then
ws.Visible = False
Else
ws.Visible = True
End If
Next ws
End SubThe above code goes through each worksheet in the workbook and checks the tab color.
It then uses an If Then Else statement to check the tab color. If the tab color is not red (RGB(255, 0, 0)), then the Visible property is set to False (thus hiding it); else, it is set to True (making the sheet visible).
Also read: Delete Sheet in Excel Using VBA
Make the Sheet Very Hidden (Cannot Be Unhidden by User)
VBA in Excel also allows you to make a sheet Very Hidden so that it cannot be unhidden by a user using the regular Excel interface.
This means that when you right-click on the sheet tab and then click on Unhide, you will not see the sheet name, and hence, you won’t be able to unhide it.
Below is the VB code that makes the sheet named Data very hidden:
Sub MakeSheetVeryHidden()
ThisWorkbook.Sheets("Data").Visible = xlSheetVeryHidden
End SubThe above code sets the Visible property of the sheet to xlSheetVeryHidden.
This is different than all the other codes where we have been setting the visible property to False or xlSheetHidden.
When you make the sheet ‘very hidden’, you will have to use a VBA code or an option in the VB editor interface to unhide it (covered later in this article).
Also read: VBA Protect / Unprotect Sheet
Check If the Worksheet is Hidden
Before using VBA code to make changes to a worksheet, it might be useful to first check whether the worksheet is hidden or not.
Below is the VBA code that checks the visible property of the worksheet and shows a message box with a numeric value (corresponding to the sheet’s visible property):
Sub CheckIfWorksheetHidden()
MsgBox Worksheets("Data").Visible
End SubThis would show a message box with one of three values:
- -1: The worksheet is visible.
- 0: The worksheet is hidden but can be unhidden via the Excel interface.
- 2: The worksheet is very hidden and cannot be unhidden via the Excel interface (only through VBA).
You can modify the code above to give you more meaningful text instead of numbers or use it as part of a larger code where the output of this line could then be used to decide what to do.
Also read: VBA Rename Sheet in Excel
VBA Codes to Unhide Sheets
And here are some example codes for unhiding sheets in different situations.
Unhide Sheets By Names
If you already know the name of the sheet that you want to unhide, you can use a code as shown below:
Sub UnhideSheetByName()
Sheets("Data").Visible = True
Sheets("Summary").Visible = True
Sheets("Table").Visible = True
End SubIn the above code, I used the sheet name and set the Visible property of that sheet to True to make it visible.
You can also use the code below, where instead of True, I use xlSheetVisible
Sub UnhideSheetByName()
Sheets("Data").Visible = xlSheetVisible
Sheets("Summary").Visible = xlSheetVisible
Sheets("Table").Visible = xlSheetVisible
End SubUnhide All Hidden Sheets
The below VBA code goes through all the sheets in the workbook and makes them visible:
Sub UnhideAllSheets()
Dim ws As Worksheet
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Unhide the sheet
ws.Visible = True
Next ws
End SubUnhide Sheets With Specific Words in the Name
Below, the VBA code would unhide all the sheets that have the word ‘Data’ in their name.
Sub UnhideSheetsContainingSpecificWord()
Dim ws As Worksheet
Dim wordCriteria As String
' Specify the word to look for in the sheet names
wordCriteria = "Data"
For Each ws In ThisWorkbook.Worksheets
If InStr(1, ws.Name, wordCriteria, vbTextCompare) > 0 Then
ws.Visible = xlSheetVisible
End If
Next ws
End SubIn the above code, I have used a variable wordCriteria to store the word that I am checking for in each sheet’s name.
It then goes through each worksheet and then uses the INSTR function to check whether the sheet name contains the word stored in the wordCriteria variable or not.
If it finds the name, it makes the sheet visible.
In this article, I’ve covered the concept of hiding and unhiding sheets in Excel using VBA and provided some simple code examples that you can use in various situations.
I hope you found this article useful. Please let me know your thoughts in the comments section below.
Other Excel articles you may also like:
