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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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.
https://analysistabs.com/hideunhide-sheets-based-on-condition/

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference