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.
Set Up the Orders Sheet
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.
Then, right click on the ExportForm sheet tab, and click Hide.
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.
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.
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.
- Right-click on the Orders sheet tab, and click View Code
- In the code window, select Worksheet, from the Object drop down list, at the top left.
- From the Procedures drop down list, select Change
- 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.
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.