Hide UnHide Worksheets in Excel VBA

Home/Excel VBA/Hide UnHide Worksheets in Excel VBA

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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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://mongopono.ru/hideunhide-sheets-based-on-condition/

LIMITED TIME OFFER
By |January 30th, 2013|Excel VBA|32 Comments

About the Author:

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.

32 Comments

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

    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.

    please advise me how to code it…

    thanks a Tons in advance:-)

    • PNRao March 24, 2015 at 9:56 PM - Reply

      Hi Ramesh,

      How do you want to hide or unhide the sheets. The below code will unhide Sheet2 AND hide Sheet1.

      Sub sbHidAndUnHideSheets()
      Sheets("Sheet2").Visible = True 'To unhide the Sheet2
      Sheets("Sheet1").Visible = False 'To hide the Sheet1 
      End Sub
      

      Hope this helps-Thanks-PNRao!

      • Ramesh March 29, 2015 at 12:12 PM - Reply

        Thanks Rao sir,

        Its perfectly working, upon the un hiding sheet 2 how to make a sheet 2 as active sheet as I have multiple sheets, currently once the code run the display sheet showing something like sheet 4/5.

        kindly advise.

        • Ramesh March 29, 2015 at 12:16 PM - Reply

          Furthermore,the hiding option should be “xlSheetVeryHidden”, kindly assist me. 🙂

          • PNRao April 1, 2015 at 7:24 PM

            You use the Activate method of worksheet:

            Sheets("SheetName").Visible = xlSheetVeryHidden
            

            Thanks-PNRao!

        • PNRao April 1, 2015 at 7:21 PM - Reply

          You use the Activate method of worksheet:

          Sheets("SheetName").Activate
          

          Thanks-PNRao!

    • SGirard February 5, 2016 at 1:36 AM - Reply

      Hi PNRao,

      I am using multiple sheet that all depends on the Data entry one. Based on one selection on the Data entry, is it possible to hide or unhide automatically specific sheet?

      I have 3 different type of Implementation. On the Data entry, i need to select the current implementation. It will modify a list that will control the content of different Sheet. I would like to hide the useless sheet and show only the ones that belong to this implementation.

      kind of:

      If ‘Client Information’B17=”Migration”
      Then Sheets(“Discovery Call”).Visible = True
      Sheets(‘Best Practice’) Visible=False

      Can you help me?

      • PNRao February 5, 2016 at 8:30 PM - Reply

        Hi Giridar,

        You can achieve this with many approaches, here are the two best methods:

        Method 1: Using Worksheet Events to hide unhide sheets

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = Range("B17").Address Then
        If Range("B17") = "Migration" Then
        sbButtonSet1
        ElseIf Range("B17") = "Other Set2" Then
        sbButtonSet2
        ElseIf Range("B17") = "Other Set3" Then
        sbButtonSet3
        Else
        sbButtonHideAll
        End If
        End If
        End Sub
        

        Method 2:This is another approach to hide unhide the sheets by using Buttons (recommended)

        Sub sbButtonSet1()
        Call ShowOnlySheets("Set1a,Set1b")
        End Sub
        Sub sbButtonSet2()
        Call ShowOnlySheets("Set2a,Set2b")
        End Sub
        Sub sbButtonSet3()
        Call ShowOnlySheets("Set3a,Set3b")
        End Sub
        Sub sbButtonHideAll()
        Call ShowOnlySheets("")
        End Sub
        Sub ShowOnlySheets(ByVal strSheets As String)
        'strSheets is parameter to specify the rqeuired sheet names (comma separated)
        'Hide all sheets except the main sheet
        For Each sht In ActiveWorkbook.Sheets
        If sht.Name <> "Main" Then sht.Visible = False
        Next
        'Unhide the required sheets
        For i = 0 To UBound(Split(strSheets, ","))
        Sheets(Split(strSheets, ",")(i)).Visible = True
        Next
        End Sub
        

        I recommend the second method as it will be user friendly, also avoids the unnecessary event calls.

        Please find the example file here:
        http://mongopono.ru/hideunhide-sheets-based-on-condition/

        Thanks-PNRao!

  2. Nicola March 30, 2015 at 4:54 PM - Reply

    How can you hide or unhide certain tabs for specific users? I was attempting the following code but the uname2 section has a syntax error.

    UserNameWindows() As String
    unamewindows = Environ(“Username”)
    Uname2 = StrConv(unamewindows, vbUpperCase)

    If Uname2 = “PERSON1” _
    Or Uname2 = “PERSON2” _
    Or Uname2 = “PERSON3” _

    Sheets(“Copy Data”).Visible = True

    End If

    • PNRao April 1, 2015 at 7:28 PM - Reply

      You are missing the ‘Then’ keyword: Please use the below code

      Sub SbShow_Or_Hide_The_Tabs_To_Specific_Users()
      Dim UserNameWindows As String
      unamewindows = Environ("Username")
      Uname2 = StrConv(unamewindows, vbUpperCase)
      If Uname2 = "PERSON1" _
      Or Uname2 = "PERSON2" _
      Or Uname2 = "PERSON3" Then
      Sheets("Copy Data").Visible = True
      End If
      End Sub
      

      Thanks-PNRao!

  3. yuko July 2, 2015 at 9:58 AM - Reply

    Hi.
    I’m used this code:
    Sub sbHidAndUnHideSheets()
    Sheets(“Sheet1”).Visible = False ‘ To hide the Sheet1
    Sheets(“Sheet2”).Visible = True ‘To unhide the Sheet2
    End Sub

    Edit for my work:
    Sub sbHidAndUnHideSheets()
    Sheets(“Khuon”).Visible = False
    Sheets(“Duc”).Visible = True
    End Sub

    Shortcut key: Ctrl + H for use this. but it isn’t work. Error with yellow highlight: Sheets(“Khuon”).Visible = False

    Pls help me!

    • PNRao July 2, 2015 at 10:16 AM - Reply

      Hi Yuko,

      Use the below code when you have only two sheets in your workbook. We can not hide all worksheets in a workbook, at least on worksheet should be visible. Just swap the statements to avoid the issue:

      Sub sbHidAndUnHideSheets1()
      Sheets("Sheet2").Visible = True 'To unhide the Sheet2
      Sheets("Sheet1").Visible = False 'To hide the Sheet1
      End Sub
      'Edit for my work:
      Sub sbHidAndUnHideSheets()
      Sheets("Duc").Visible = True
      Sheets("Khuon").Visible = False
      End Sub
      

      Thanks-PNRao!

  4. yuko July 2, 2015 at 2:17 PM - Reply

    Hi PNRao!
    Thank you so much!

  5. David July 18, 2015 at 1:50 AM - Reply

    Hi,
    This has been really useful, want can I do if I want to have a worksheet with a dropdown menu where I could select the tab I want to be unhidden, and the rest of them hidden?

    Thanks

    David

    • PNRao July 18, 2015 at 2:09 PM - Reply

      Hi David,
      Yes, this is possible. You can fill the drop down while opening the workbook

      Place a Combo Box in the required worksheet (Example: in Sheet1). And place the below code in ThisWorkbook Code module:

      Private Sub Workbook_Open()
      Sheets("Sheet1").ComboBox1.Clear
      For Each sht In ThisWorkbook.Sheets
      Sheets("Sheet1").ComboBox1.AddItem sht.Name
      Next
      End Sub
      

      And place the below code in the Worksheet module (i.e: Sheet1 Code module):

      Private Sub ComboBox1_Change()
      If Not ComboBox1.ListIndex >= 0 Then Exit Sub
      'Unhide/show the required sheet
      Sheets(ComboBox1.Value).Visible = True
      'hide all other sheets
      For Each sht In ThisWorkbook.Sheets
      If sht.Name <> ComboBox1.Value Then sht.Visible = False
      Next
      'You may want Sheet1 should be visible always
      Sheets("Sheet1").Visible = True
      Sheets("Sheet1").Activate
      End Sub
      

      This will make you to select required worksheet visible and hide all other sheets.

      Thanks-PNRao!

      • David July 21, 2015 at 12:53 AM - Reply

        Thanks PNRao!

        I have ben tying with this but can’t get it wo work for me… I am not using a private sub so I can just run it anytime with a specific command.
        The message I am getting is that there is an Object Required. My ComboBox y also named ComboBox1.
        Also if my sheets are already named, do I need the first part of code you sent?

        Thanks a lot!

        • PNRao July 23, 2015 at 1:29 PM - Reply

          Hi David,
          Yes, you need to both the codes.

          The first code will goes to ThisWorkbook Module:(go to Project explorer and and double click on the ThisWorkbook class module and paste the code)

          And the second code goes to your worksheet Module:(go to Project explorer and and double click on the required Sheet class module and paste the code)

          Hope this helps!
          Thanks-PNRao!

  6. Dan Bailey August 24, 2015 at 8:39 PM - Reply

    I working on an application with 6 sheets that interact with each other via code. I do not want the users to be able to see the sheet tabs BUT, I need to be able to interact with them. I’m using Excel 2013 (xlsm file) and went into the File- Options-Advanced and deselected “Sheet Tabs visible”. The tabs are hidden and I can still access the sheet. However, after I save and close the workbook and then reopen it the sheet tabs are there again. I am at a lose as to why the sheet tab return. Is there some VBA code that can duplicate what the Options-Advance setting should keep in the workbook but does not.? I would be so grateful if someone could help me out on this one.

  7. Dan Bailey August 24, 2015 at 9:34 PM - Reply

    I have discovered the problem. The file I need to hide the “Sheet Tabs” only is a “XLSM” micro enabled workbook. I opened a “XLSX” file and tested the Options – Advanced and deselected “Sheet Tabs visible” and they went away. After I save and reopened the file the Tabs were still hidden. Is there any way to hide the Tabs only in a “XLSM” workbook?? Or is there anyway I can use a “XLSX” workbook and write VBA code in it. Thanks. Dan

    • PNRao August 25, 2015 at 12:30 AM - Reply

      Hi,
      I have updated the post, please see the last example to make worksheets Very Hidden.
      Thanks-PNRao!

  8. Priya October 28, 2015 at 3:01 PM - Reply

    Hi,

    I have index sheet from which i want to access other sheets.. I have different sheets for expenses, payroll, taxes and so on and i have given hyperlink to all these sheets from the index page.
    I want to hide all the sheets except the index..and from here when the user clicks on a desired link.. it should take the user to the specified sheet. Even it is hidden. Please help

    Priya

  9. samola November 6, 2015 at 2:36 AM - Reply

    Hello,

    I am trying to hide all sheets except two, below is what I have to hide only one sheet. I need to expand it so that I can keep “approval” visible.

    Dim wk As Worksheet
    For Each wk In Worksheets
    If Not wk.Name = “AdviceForm” Then wk.Visible = xlSheetHidden

    Next

    End Sub

    Thanks for your assistance in this matter.

    Regards,
    Samola

  10. November 12, 2015 at 10:59 AM - Reply

    Hi,

    I would like to hide all sheet except the summary tab, how to do it? Please advice. Thanks

    Deepu

    • PNRao November 16, 2015 at 4:08 PM - Reply

      Here is code to hide all sheets except one summary sheet:

      Sub sbHideAllExceptSummary()
      Sheets("Summary").Visible = True
      For Each sht In ActiveWorkbook.Sheets
      If sht.Name <> "Summary" Then sht.Visible = False
      Next
      End Sub
      

      Thanks-PNRao!

  11. Sammie December 6, 2015 at 3:46 PM - Reply

    Hi,
    Please I’m working on a daily report, how can I create a button on that will lead the user to another sheet were they can fill in details? Please help really urgent.
    Thank you.

  12. Venkat March 3, 2016 at 3:12 PM - Reply

    Hi
    I have created navigation in home page. But the thing I want is to hide the sheet names which are shown below. But it should be accessed when the buttons are clicked.

    Simply to say “Sheets should be hidden, but should be accessible”

    Could you please help me in this issue.

    Thanks in advance.

  13. Jorge April 20, 2016 at 8:04 PM - Reply

    Hi,
    In regards to hiding or unhiding- I know I can do that with a sheet, but can I hide a condition inside a sheet?
    This is the macro that I’m using and it pops “Run-time error ‘438’: Object doesn’t support this property or method”
    Sub TransferStuff()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Range(“M1”, Range(“M” & Rows.Count).End(xlUp)).AutoFilter 1, “”
    Range(“A2”, Range(“L” & Rows.Count).End(xlUp)).Copy Sheet2.Range(“A” & Rows.Count).End(3)(2)
    Range(“A2”, Range(“L” & Rows.Count).End(xlUp)).Visible = False
    [M1].AutoFilter

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Sheet2.Select

    End Sub

  14. Colin August 26, 2016 at 5:40 PM - Reply

    Hi,

    I keep getting an error at the point below. I’m quite new to this so still learning.

    For i = 0 To UBound(Split(strSheets, “,”))
    Sheets(Split(strSheets, “,”)(i)).Visible = True

    I’m not sure why, any help would be appreciated.

    Thanks

    C

  15. brendan November 23, 2016 at 4:51 PM - Reply

    Hi

    I would like to be able to create a button on a home page to open a hidden sheet which needs a password to gain entry. (either separate buttons or a drop down list)
    I have 1 sheet as the main page and 6 other sheets for each individuals personal xmas savings.

    How would i do this?

  16. Matt March 7, 2017 at 8:57 PM - Reply

    Hi PNRao,

    I’ve got a workbook that presently has 38 pages. I’ve included code to create a dynamic index.
    Private Sub Worksheet_Activate()
    Dim xSheet As Worksheet
    Dim xRow As Integer
    Dim calcState As Long
    Dim scrUpdateState As Long
    Application.ScreenUpdating = False
    xRow = 1
    With Me
    .Columns(1).ClearContents
    .Cells(1, 1) = “INDEX”
    .Cells(1, 1).Name = “Index”
    End With
    For Each xSheet In Application.Worksheets
    If xSheet.Name Me.Name Then
    xRow = xRow + 1
    With xSheet
    .Range(“A1”).Name = “Start_” & xSheet.Index
    .Hyperlinks.Add anchor:=.Range(“A1″), Address:=””, _
    SubAddress:=”Index”, TextToDisplay:=”Back to Index”
    End With
    Me.Hyperlinks.Add anchor:=Me.Cells(xRow, 1), Address:=””, _
    SubAddress:=”Start_” & xSheet.Index, TextToDisplay:=xSheet.Name
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    When the index is created it links back to the index page but it is deleting a line I am trying to keep. I would like to insert a row before creating the link back to the index. Also when creating the index I would like to have it auto generate a checkbox to be used to hide worksheets not needed for the project we are working on.

    I would like to use the same checkbox to hide rows on the cover sheet as well.

    If you could provide any help it would be immensely appreciated.

  17. Ali March 8, 2017 at 3:55 AM - Reply

    Hi

    I have 2 Sheets Sheet 1(Home) and Sheet 2(Info) .
    How can i to hide Sheet2 and create a macro in Sheet1 for unhide that, When i go back to Sheet1 automatically Sheet2 automatically be hidden ??????????

    please help me.

Leave A Comment


Related pages


countif function in excelvba break statementunprotect cells in excel 2010vba paste specialbreak excel workbook passwordvlookup simplepassword protect excel workbookremove blanks excelcount if formula excelpivots excelexcel vba delete rowexcel control commandsunmerge cells excelcreating a userform in excelhow to set macro security in excel 2010dynamic dashboard excelvba access excelmicrosoft excel functions tutorialdata type in vbavba color codesvb6 msgboxvba redim arrayvb program for calculatorvba code to close a formsqlfunctionsremove blanks excelexcel pivot table tutorialsdelete duplicate lines in excelexcel estimating spreadsheethyperlink codingduplicate formula excelmicrosoft excel countif functionsas macros tutorialvba advanced filterexcel costing templateinsert a new worksheet in excelconvert csv to xml excelvba create queryvba code to unprotect sheet with passwordexcel vba offset functionresource planning spreadsheet excelsql basic commandsif statement in vbaexcel formulas lookup and referencedml languagehow many rows and columns in excel 2007automatically insert rows in excelmsgbox macroexcel iferror functionopen excel file in vbaexcel vba sheets.addmacros for excel 2007duplicate in excel 2007most useful programssample project plan template excelexcel vba nested ifhow to use a sumifvba excel close userformwww excel shortcut keysvba clear arrayvba lookup tablevba object oriented programminghow to create a dashboard in excel 2007number of rows in excel 2007job estimate template excelcount cells in vbaexcel count duplicates in columncopy worksheet to another workbookms excel short cut keysprint function vbamacro excel open fileado connectionstringhow to use iferror formula in excelexcel vba tutorialcountif function in excel 2007how to delete all blank rows in excelvba trim