You can use a VBA code to hide or unhide a sheet in Excel. When you right-click on the sheet tab, you can see the option to hide or unhide it, and that same thing you can do with a VBA code.
In this post, we will look at some of the ways and methods that we can use.
VBA Code to Hide a Sheet
Let’s say you want to hide “Sheet1” from the active workbook. In that case, you need to use code like the following.
Sheets("Sheet1").Visible = False
In the above code, you have referred to Sheet1, use the visible property, and changed it to false.
Make a Sheet Very Hidden
There’s one more option that you can use to make a sheet very hidden that cannot be un-hide by the user easily.
Hide a Sheet Based on the Value from a Cell
Alright, if you want to use a cell value instead of directly using the sheet name in the code, you can refer to that cell.
Sheets(Range("A1").Value).Visible = True
This code refers to cell A1 and uses the value from it to refer to the sheet that you want to hide.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Check Sheet Before Hiding
You can also use a small code like the following to check the sheet that you want to hide exits or not.
Sub vba_hide_sheet()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
If sht.Name = "Sheet1" Then
sht.Visible = False
Exit Sub
End If
Next sht
MsgBox "Sheet not found", vbCritical, "Error"
End Sub
The above code uses the FOR EACH LOOP + IF STATEMENT to loop through each sheet in the workbook. And check for the sheet that you want to hide.
Hide All the Sheets (Except ActiveSheet)
Now there one thing that you need to understand you can’t hide all the sheets. There must be one sheet visible all the time.
Sub vba_hide_sheet()
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
The above code loops through all the sheets of the workbook, then match each sheet’s name with the active sheet’s name and hide it if it doesn’t match.
VBA Code to Unhide a Sheet
To unhide a sheet, you need to change the visible property to TRUE.
Sheets("Sheet1").Visible = False
If the sheet that you want to unhide it already visible, this code won’t show any error. But if that sheet doesn’t exist, then you’ll get a Run-time error ‘9’.
Use VBA to Unhide All the Hidden Sheets
Imagine you have more than one hidden sheet in a workbook, and if you want to hide them manually, you need to do this one by one.
But here’s the code does this in one go.
Sub vba_unhide_sheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Visible = False Then
ws.Visible = True
End If
Next ws
End Sub
It loops through each sheet and un-hides it.
More Tutorials on VBA Worksheets
- How to Insert a New Sheet using VBA
- How to Activate a Sheet using VBA
- How to Copy a Sheet using VBA
- How to Rename a Sheet using VBA
- How to Delete a Sheet using VBA
- How to Count Sheets using VBA
- How to Protect a Sheet using VBA
- How to Clear an Entire Sheet using VBA
- How to Check is a Sheet Exists using VBA
- How to Loop Through Sheets in a Workbook
- Back to VBA Worksheet / VBA Tutorial