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

Premium Project Management Templates

50+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

50+ Project Management Templates Pack

Excel PowerPoint Word

VIEW DETAILS

Ultimate Project Management Template – Advanced

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

By Published On: January 30th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

37 Comments

  1. Ramesh March 24, 2015 at 7:45 PM

    I would like to use the both Hide and Unhide in one VBA, as I would like to hide the active/current sheet and unhide the specified sheet.

    plea