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
____________