Hide an Excel Worksheet Automatically

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.

hidesheetvba01

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

hidesheetvba02

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.

hidesheetvba03

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

__________________

You may also like...

2 Responses

  1. Terri says:

    How can you set this so two sheets can become visible with one selection?

  1. June 23, 2014

    […] a bit of programming, you can automatically show or hide a worksheet, based on changing a cell. For example, select “Canada” and the Export Form sheet […]

Leave a Reply

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