How to Hide Sheets with Yes/No Dropdown

[JBA]

I have created a drop down list in Excel with the terms "Yes" and "No." I would like to have a separate Excel worksheet hide/un-hide based upon the selection. For example, if "Yes" is selected I would like the worksheet to be displayed. If "No" is selected I would like the worksheet to be hidden. Any suggestions on how I can accomplish this task?

Thanks - JBA

 

[circledchicken]

Hi JBA,

Try the following code (for the sheet with the drop down list, right-click on the sheet tab name at the bottom and click on 'View Code' and paste in the below):

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Range("A1") = "Yes" Then
        Sheets("Sheet2").Visible = True
    Else
        Sheets("Sheet2").Visible = False
    End If
End If

End Sub

You should substitute "A1" in the above for the cell with your drop-down list, and "Sheet2" with the name of the sheet you want to display/hide.

Here's a nice article describing Worksheet Change Events:
http://www.ozgrid.com/VBA/run-macros-change.htm

 

[HalfAce]

circledchicken has answered your question exactly as asked, but do you really need to do this with the worksheet change event? (You realize this code gets fired every single time any changes happen anywhere on the sheet, right?)

Another option might be to just use a button (from the Forms toolbar) and assign code something like this to it. That way it only fires when the user clicks the button.

Sub ShowHideButton()
ActiveSheet.Shapes("Button 1").Select
Select Case Selection.Characters.Text
  Case "Show"
    Sheets("Sheet2").Visible = True
    ActiveSheet.Shapes("Button 1").Select
    Selection.Characters.Text = "Hide"
  Case "Hide"
    Sheets("Sheet2").Visible = False
    ActiveSheet.Shapes("Button 1").Select
    Selection.Characters.Text = "Show"
  Case Else
End Select
SendKeys "{ESC}"
End Sub

Of course, you'll need to use the button's real name and the real name of the sheet to show/hide.

Hope it helps.

 

[circledchicken]

Thats a helpful point - thanks! I should avoid using worksheet events where avoidable.

 

[cstimart]

circledchicken has answered your question exactly as asked, but do you really need to do this with the worksheet change event? (You realize this code gets fired every single time any changes happen anywhere on the sheet, right?)

Another option might be to just use a button (from the Forms toolbar) and assign code something like this to it. That way it only fires when the user clicks the button.

Sub ShowHideButton()
ActiveSheet.Shapes("Button 1").Select
Select Case Selection.Characters.Text
  Case "Show"
    Sheets("Sheet2").Visible = True
    ActiveSheet.Shapes("Button 1").Select
    Selection.Characters.Text = "Hide"
  Case "Hide"
    Sheets("Sheet2").Visible = False
    ActiveSheet.Shapes("Button 1").Select
    Selection.Characters.Text = "Show"
  Case Else
End Select
SendKeys "{ESC}"
End Sub

Of course, you'll need to use the button's real name and the real name of the sheet to show/hide.

Hope it helps.

I feel stupid for asking but can you explain this one?

Will this work with a command button, which changing "Button 1" to CommandButton1? I tried it but it stops with the Select Case section. Is the selection the button data...or the selected cell...or something else?

 

[HalfAce]

First of all, no feeling stupid. The only stupid question is the one that never got asked.
1) No, that code won't work with a command button. (But the code below will.)
2) The 'selection' in the code above refers to the actual button itself, not the data or cell but the actual button object.

I'm glad you're wanting to use a commandbutton instead of the Forms button. (Most folks just go for the Forms first.)

For using CommandButton1, you'll want to put this code in the sheet module (as opposed to a standard module).

Private Sub CommandButton1_Click()
With CommandButton1
  Select Case .Caption
    Case "Hide"
      Sheets("Sheet2").Visible = False
      .Caption = "Show"
    Case "Show"
      Sheets("Sheet2").Visible = True
      .Caption = "Hide"
  End Select
End With
End Sub

Also, I forgot to include this earlier, but (as I'm sure you've figured out...) you'll also need to make sure the button caption is either 'Hide' or 'Show' before either routine will hide or unhide the sheet.

Hope it helps.

 

[JBA]

Thanks very helpful. How could the code be edited for a check box? If the check box is selected, display the sheet. If the check box is not selected, hide the sheet?