Background
In Excel, we can hide and unhide worksheet as we wish.
Read How to Hide Excel Sheet so it can’t be Found easily to learn the best way to hide Excel sheets.
What if we would like to do it by VBA? What is the property dedicated to control the visibility of Excel worksheet?
The answer is “Visible” property.
In this article, I will show you how to use the “Visible” property and VBA script to play around with it.
Sample Workbook
Download the workbook to practice it by yourself!
Press the download button!
3 options for Visible property
There are 3 options for the “Visible” property.
- xlSheetVisible
- xlSheetHidden
- xlSheetVeryHidden
xlSheetVisible is relatively easy to understand.
As its name suggests, it makes a worksheet visible. It is that easy.
By default, a sheet’s property should be xlSheetVisible.
What about the difference between xlSheetHidden and xlSheetVeryHidden?
You may be interested in How To Remove Digits After Decimal In Excel?
Difference between xlSheetHidden and xlSheetVeryHidden
What makes most people confused is the difference between xlSheetHidden and xlSheetVeryHidden.
Briefly speaking, xlSheetVeryHidden hides a worksheet better than xlSheetHidden.
Let me show you with an example.
In this example, I have got 3 sheets in the workbook. They are “Dollar Excel links”, “First sheet” and “Second sheet” respectively.
Let me try to hide the “First sheet” with xlSheetHidden and the “Second sheet” with xlSheetVeryHidden.
You may not realise the difference immediately after you use these two options to hide a sheet.
When you try to unhide sheets made hidden by these two options, you will notice the difference.
Now I try to unhide sheets as usual. There is only “First sheet” in the “Unhide” dialog. “Second sheet” is gone.
You may be interested in How to remove all conditional formatting from a workbook?
How to unhide sheets?
xlSheetHidden
When you set the “Visible” property as xlSheetHidden, you can just unhide the sheet by the usual way. Open the unhide sheet dialog and select the sheet made hidden.
xlSheetVeryHidden
When you set the “Visible” property as xlSheetVeryHidden, things get a lot more complicated. You couldn’t unhide the sheet by usual. There are only two options available for you.
- Tweak the worksheet property
- By VBA
VBA code
Read How to Insert & Run VBA code in Excel – VBA101 if you forget how to insert VBA code in Excel.
You can choose to use constant or number in your VBA code.
Constant
Sub Unhide_sheets_constant()
'The script is created by Dollar Excel.
'Subscribe to Dollar Excel newsletter for more free resources like this.
Sheets("Dollar Excel links").Visible = xlSheetVisible
Sheets("First sheet").Visible = xlSheetHidden
Sheets("Second sheet").Visible = xlSheetVeryHidden
End Sub
Number
Sub Unhidesheets()
'The script is created by Dollar Excel.
'Subscribe to Dollar Excel newsletter for more free resources like this.
Sheets("Dollar Excel links").Visible = -1
Sheets("First sheet").Visible = 0
Sheets("Second sheet").Visible = 2
End Sub
Do you find this article helpful? Subscribe to our newsletter to get exclusive Excel tips!
