When you build a workbook for other people to use, there might be worksheets that can stay hidden some of the time. In this example, the workbook is used to create and print orders.
If the shipment is going to Canada, an Export Form needs to be printed too. For shipments to the USA, the form isn’t needed.
Hide the Sheet with VBA
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
The code included the country name and sheet name, and showed the ExportForm sheet when Canada was 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
Shorten the Code
Rick Rothstein suggested shortening the code, to get rid of the second If…End If section.
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
Use Named Ranges
Last week, BizJack added a comment, asking if variables could be used, instead of hard coding the country and sheet names. He wanted to refer to the values in named ranges on a worksheet.
That’s a great idea, so I’ve created an updated example, using Rick’s suggestion for shortening the code, and adding variables.
The Country and Sheet Name are in named ranges on the Lists sheet, along with a list of the two countries.
- Cell C3 is named rngCountry
- Cell C5 is named rngSheet
- Range E3:E4 is named Countries
Update the Code With Variables
Here is the revised code, using variables for the country and sheet name. If you start shipping to Japan, instead of Canada, just change the country name on the Lists sheet.
Private Sub Worksheet_Change(ByVal Target As Range) Dim wb As Workbook Dim Country As String Dim ShtName As String Set wb = ThisWorkbook Country = wb.Names("rngCountry").RefersToRange.Value ShtName = wb.Names("rngSheet").RefersToRange.Value If Target.Address = Me.Range("CountrySel").Address Then Sheets(ShtName).Visible = Target.Value = Country End If End Sub
Test the Revised Code
To test the code, select a country from the drop down list on the OrderForm sheet. The code still works the same as it did previously:
- select USA, and the ExportForm sheet is hidden
- select Canada, and the ExportForm sheet is visible
The only difference is that it’s easier to change either of those values – just go to the Lists sheet, instead of revising the VBA code.
Download the Sample File
To see how the code works, you can download the sample file. Go to the Excel Files page on my Contextures website. In the UserForms and VBA section, look for UF0027 – Hide Sheet When Cell Changes