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.

Code:
Sub sbHideASheet()
Sheet2.Visible = False
'OR You can mention the Sheet name
Sheets("Sheet2").Visible = True
End Sub
Observations:

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.

Hide Unhide Worksheet Examples 1

Hide Unhide Worksheet Examples

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.

Hide Unhide Worksheet Examples 3

Code:
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.
VBA to hide unhide sheets based on conditions Selection change

Download the Example VBA file here and explore your self.
http://analysistabs.com/hideunhide-sheets-based-on-condition/

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!