Hide Excel Sheet When Cell Changes

In an Excel workbook, you might have some sheets that you don’t need to see all the time. Instead of manually hiding or showing the sheets, you can use Excel VBA to automatically hide or show them.

In this example, the workbook has an order sheet, and there is a country selector at the top of the sheet. If Canada is selected, we need to print the ExportForm sheet, as well as the Orders sheet.

excelsheetshide01

Set Up the Orders Sheet

On the Orders sheet, the Country drop down list is created with Excel data validation. Then, that cell was named as CountrySel.

excelsheetshide02

Hide and Unhide the Sheet

We’ll be adding some Excel VBA code to hide and unhide the ExportForm sheet. To see what the code should look like, you can turn on the Excel macro recorder, store the macro in a new workbook, and click OK.

Excelmacronewworkbook

Then, right click on the ExportForm sheet tab, and click Hide.

excelsheetshide03

To see the sheet again, click on the Orders sheet tab, and click Unhide. In the Unhide dialog box, click on the ExportForm sheet name, and click OK.

excelsheetshide04

Turn off the macro recorder, and press Alt+F11, to open the Excel Visual Basic Editor.

In the modules for your new workbook, you should find the recorded code, that looks similar to the code shown below.

excelsheetshide05

This shows that we can set the sheet’s Visible property to True or False, to show or hide the sheet.

Now that you’ve seen the syntax, you can close the workbook where you recorded the show/hide macro, without saving it, and return to the Orders workbook in Excel.

Automate the Sheet Hiding Code

To make the Excel VBA code run automatically, we’ll use the Worksheet_Change event, on the Orders sheet.

  1. Right-click on the Orders sheet tab, and click View Code
  2. In the code window, select Worksheet, from the Object drop down list, at the top left.
  3. From the Procedures drop down list, select Change
  4. Where the cursor is flashing, add the code shown below. The code will check the address of the cell that was changed, and show the ExportForm sheet if Canada is selected.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("CountrySel").Address Then
    If Target.Value = "Canada" Then
        Sheets("ExportForm").Visible = True
    Else
        Sheets("ExportForm").Visible = False
    End If
End If
End Sub

The finished code will look like the procedure shown in the screen shot below.

excelsheetshide06

Test the Sheet Hiding Code

Back in Excel, select a country name from the drop down list, and the ExportForm sheet should show or hide automatically.

excelsheetshide07

____________

You may also like...

15 Responses

  1. Eric says:

    There is an Excel VBA enumeration, xlSheetVisibility, which consists of:

    xlSheetHidden
    Value: 0 (FALSE)
    Defn: Hides the worksheet which the user can unhide via menu.
    xlSheetVeryHidden
    Value: 2
    Defn: Hides the object so that the only way for you to make it visible again is by setting this property
    to True (the user cannot make the object visible).
    xlSheetVisible
    Value: -1 (TRUE)
    Defn: Displays the sheet.

    I haven’t used them much, and have defaulted on using TRUE or FALSE for Sheets().Visibility
    Has anyone – Debra? – used them and seen that xlSheetVeryHidden acts as intended?

  2. @Eric, yes that works nicely, and I use it to hide Admin sheets (with lists, etc.) that most users shouldn’t touch. Those sheets don’t appear in the Unhide window, and can be unhidden with a macro or in the VBE.

    Sub HideSheets()
    Dim ws As Worksheet
    On Error Resume Next
    
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 5) = “Admin” Then
            ws.Visible = xlSheetVeryHidden
        Else
            ws.Visible = xlSheetVisible
        End If
    Next ws
    
    Worksheets(1).Activate
    
    End Sub
  3. Rick Rothstein (MVP - Excel) says:

    @Deb,

    For your Worksheet_Change event code, as long as you have to evaluate:

    Target.Value = “Canada”

    you might has well use its results directly and shorten your code somewhat:

    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = Me.Range(“CountrySel”).Address Then
        Sheets(“ExportForm”).Visible = Target.Value = “Canada”
      End If
    End Sub

    As for the HideSheets subroutine, we can do something similar to shorten the code (although it looks a tad “sloppier” than the above code)…

    Sub HideSheets()
      Dim ws As Worksheet
      On Error Resume Next
      For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible – 1.5 * xlSheetVeryHidden * (Left(ws, 5) =  “Admin”)
      Next ws
      Worksheets(1).Activate
    End Sub
  4. Rick Rothstein (MVP - Excel) says:

    @Deb,

    Not sure what happened, but there is supposed to be an equal sign between Left(ws,5) and “Admin” at the end of the statement inside the For..Next block for the HideSheet macro.

  5. Asko says:

    Hi,

    I tried to make this work in my model but faced some problems. In my case I need to hide several sheets depending on the text in target cell which is not created by data validation (don’t know if this matters). I have three options for the target cell text (“MP”, “KP” or “SV”). The target cell uses VLOOKUP function to get the right text.

    I haven’t used VBA a lot. I tried to follow instructions step by step in new workbook too but didn’t get this work either. Can you help me?

    • Asko says:

      Now it works in new workbook. I found a mistake in the code. I’ll try this once again in my original model.

    • Asko says:

      Here is a description of my situation:

      – I have three product groups
      – When I choose the right product from the drop down list, I want the reference product group to appear in a target cell (For doing this I’ve used VLOOKUP function)
      – When I’ve chosen the product (from the drop down list) I want all other product group sheets to be hidden.
      – If I choose another product (another product group) from the drop down list I want that product group sheets visible and other hidden

      I’ve now tried this without VLOOKUP by writing the product group to target cell and I’m almost there. Each product group seems to be working one at the time but when I first write the product group name to cell and then write the other product group name WITHOUT deleting the first one, too many sheets disappears. So I need to first delete the target cell before entering the new one.

      Code looks like this

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = Me.Range(“productgroup”).Address Then
      If Target.Value = “MP” Then
      Sheets(“Sheet1KP”).Visible = False
      Sheets(“Sheet1SV”).Visible = False
      Sheets(“Sheet2KP”).Visible = False
      Sheets(“Sheet2SV”).Visible = False
      Sheets(“Sheet3KP”).Visible = False
      Sheets(“Sheet3SV”).Visible = False
      Sheets(“Sheet4KP”).Visible = False
      Sheets(“Sheet4SV”).Visible = False
      ElseIf Target.Value = “KP” Then
      Sheets(“Sheet1MP”).Visible = False
      Sheets(“Sheet1SV”).Visible = False
      Sheets(“Sheet2MP”).Visible = False
      Sheets(“Sheet2SV”).Visible = False
      Sheets(“Sheet3MP”).Visible = False
      Sheets(“Sheet3SV”).Visible = False
      Sheets(“Sheet4MP”).Visible = False
      Sheets(“Sheet4SV”).Visible = False
      ElseIf Target.Value = “SV” Then
      Sheets(“Sheet1MP”).Visible = False
      Sheets(“Sheet1KP”).Visible = False
      Sheets(“Sheet2MP”).Visible = False
      Sheets(“Sheet2KP”).Visible = False
      Sheets(“Sheet3MP”).Visible = False
      Sheets(“Sheet3KP”).Visible = False
      Sheets(“Sheet4MP”).Visible = False
      Sheets(“Sheet4KP”).Visible = False
      Else
      Sheets(“Sheet1MP”).Visible = True
      Sheets(“Sheet1KP”).Visible = True
      Sheets(“Sheet1SV”).Visible = True
      Sheets(“Sheet2MP”).Visible = True
      Sheets(“Sheet2KP”).Visible = True
      Sheets(“Sheet2SV”).Visible = True
      Sheets(“Sheet3MP”).Visible = True
      Sheets(“Sheet3KP”).Visible = True
      Sheets(“Sheet3SV”).Visible = True
      Sheets(“Sheet4MP”).Visible = True
      Sheets(“Sheet4KP”).Visible = True
      Sheets(“Sheet4SV”).Visible = True
      End If
      End If
      End Sub

  6. BizJack says:

    Thanks, Debra.

    1) How do you pass the name of the sheets as variables declared in the UI via the Name Manager, instead of fixed values in the VBA itself?
    In your example, that would be to replace “ExportForm” by a variable somewhere in a sheet, with this variable containing the name of the sheet.

    2) Same question for the value to compare to.
    In your example, that would be to replace “Canada” by a variable somewhere in a sheet, with this variable containing the value “Canada”.

    In advance, thank you!

    • @BizJack, you could use something like this:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Country As String
      Dim ShtName As String
      
      Country = ThisWorkbook.Names("rngCountry").RefersToRange.Value
      ShtName = ThisWorkbook.Names("rngSheet").RefersToRange.Value
      
      If Target.Address = Me.Range("CountrySel").Address Then
          Sheets(ShtName).Visible = Target.Value = Country
      End If
      
      End Sub
  7. BizJack says:

    Thank you!
    :)

  8. Tony says:

    Hi,

    I’m new at this, only been using VBA since Nov 14.

    I’m trying to hide sheets that are not required based on the information found in 1 cell. The info in this cell come from another macro. There are multiple answer that it can be. I don’t need it to be named Private as only my team will be using it, its just to speed up our manual process we currently using.

    I have tried many different codes that work nicely on 1 name but when I duplicate the code for all 22 answers I get errors. The one below works best but its based on the Username not the cell name.

    Sub Machines()

    Excel UserName

    [E22] = Application.UserName

    Windows UserName

    WinUser = Environ(“MACHINE”)

    MsgBox “Application Username: ” & OfficeUserName & Chr$(10) _
    & “Windows Username: ” & WinUser

    End Sub

    Sub Private_Auto_Unhide_FMC_1()

    ‘ This macro unhides sheets when the user set below opens the workbook

    ‘ It is a Private Sub to prevent it being listed with other macros

    On Error Resume Next

    ‘ First sheet to be made visible

    Do
    If Application.UserName = “FMC 1” Then Worksheets(“FMC’s & SCH’s (Stamped”).Visible = True

    End If

    End Sub

    Sub Private_Auto_Hide_FMC_1()

    ‘ This macro ensures the sheets are hidden when any user closes the workbook

    On Error Resume Next

    ‘ Hide worksheet 1

    Worksheets(“TM’s (Rolled)”).Visible = xlSheetVeryHidden

    ‘ Hide worksheet 2

    Worksheets(“Lines 40, 41 & 42 (Rolled)”).Visible = xlSheetVeryHidden

    ‘ Hide worksheet 3

    Worksheets(“L40, L41 & L42 (Rolled) Offline”).Visible = xlSheetVeryHidden

    End Sub

    Regards
    Tony

    • Tony, you could use Select Case to check the UserName, and list all the names that should result in the sheet being visible.

      For example:

      ' First sheet to be made visible
      Select Case Application.UserName
        Case "FMC 1", "ABC", "Test", "Tony"
          Worksheets("Sheet2").Visible = True
        Case Else
          Worksheets("Sheet2").Visible = False
      End Select
  9. Tony says:

    Many thanks,

    I’ll try this today.

    Regards
    Tony

  1. June 17, 2014

    […] In 2011, I shared some code that automatically shows or hides a worksheet, based on what you selected from a drop down list. You can see the original article here: Hide Excel Sheet When Cell Changes […]

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *