VBA hide unhide worksheets example macro macro helps when we have many worksheets in a workbook and you want to show only specific worksheets to the user. You can hide unhide worksheets using Excel VBA. For Example you may be developing a tracker for different departments in an organization. Like HR, Admin, Finance, etc…, all of these may have same knind of data but the data (numbers) may vary from one department to another.
While sending the workbook to a specific department, you need to show the worksheets related to that particular department. And have hide all other worksheets, it may be confidential or not useful to that department.
VBA hide unhide worksheets – Solution
We can use Visible=FALSE to Hide a Worksheet, Visible=TRUE to UnHide a Worksheet
Hide UnHide Worksheets in Excel VBA – An Example to Hide the Worksheets
The following example will show you how to hide and unhide the worksheet using Excel VBA.
Sub sbHideASheet() Sheet2.Visible = False 'OR You can mention the Sheet name Sheets("Sheet2").Visible = True End Sub
When you hide by setting the Visible property is FALSE, it will be available for user to Unhide the Worksheets. User can right click on the Sheet tabs and Unhide the Worksheets as shown below.
How Hide the Worksheets, so that user can not unhide the Worksheets?:
Yes, we can hide the worksheets completely by Changing the visual property. You can set the visual property to hide the worksheets, so that user can not unhide it by right click on the Sheets Tabs. You can see the different options of hiding and unhiding the sheets in the following screen-shot.
Sub sbHideASheet() Sheet2.Visible = 2 'to very hide the worksheet 'OR You can mention the Sheet name Sheets("Sheet2").Visible = True End Sub
Once you are done with this, you can protect the VBA project by setting the password to open it. So that user can not code it to open the Worksheets.
Advanced Hide Options
When we hide worksheets using, still user can right click on tabs and un-hide the worksheets. For example, following example will hide the worksheet and user can un hide the sheets on right click on sheet tabs:
Sub sbHideSheet() Sheets("SheetName").Visible = False 'OR Sheets("SheetName").Visible = xlSheetHidden End Sub
What if you do not want to permit users to un-hide worksheet, you can set the Visible property of worksheet to xlSheetVeryHidden and lock the VBA code. so that user can not un-hide the worksheet. The below example will hide the sheet and user can not see it in un hide worksheet dialog list.
Sub sbVeryHiddenSheet() Sheets("SheetName").Visible = xlSheetVeryHidden End Sub
Hide Unhide sheets based on Condition (Selection Change) And Button Click
The below example file helps you to understand how to hide or unhide the sheets based on a codition (Range/Selection change), I have also shown another approach using simple buttons.
Download the Example VBA file here and explore your self.