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.

15 Responses

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

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

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

@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

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?

– 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.

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”.

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

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.

[…] 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 […]