Need assistance with VBA

[Cerber1983]

Good morning!

I need someone's assistance with hide/unhide tabs of my workbook. Tabs are displayed or hidden based on drop-down value of a cell on the main page

Majority of states will be sharing same "Most States" tab and I am looking for VBA to hide/unhide tabs for special states.

For example: If I am selecting CA or WA (one of special states) in Cell C5 as per example above, I would like to get "CA Main" or "WA Main" tab to be displayed and "Most States" tab to be hidden. Or if it is NOT special state like CA or WA, "Most States" tab to be displayed and CA or WA to be hidden.

Below code seems to be working but the problem I am running into is that if I am selecting CA in cell C5, it brings both tabs up: "Most States" and "CA Main".

Below is VBA code that I have used. Please help!

Private Sub Worksheet_Change(ByVal Target As Range)
If [C5] = "CA" Then
Sheets("CA Main").Visible = True
Else
Sheets("CA Main").Visible = False
Sheets("Most States").Visible = True
End If
If [C5] = "CO" Then
Sheets("CO Main").Visible = True
Sheets("Most States").Visible = False
Else
Sheets("Most States").Visible = True
Sheets("CO Main").Visible = False
End If

 

[Bosquedeguate]

Try something like this ... Alway turn on (make visible Most States). Then hide it in your special cases.

I modified your code a bit to simplify it and get rid of the IF THEN ELSEs

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Address = "$C$5" Then
      Sheets("Most States").Visible = True
      Select Case .Value
        Case "CA"
          Sheets("CA Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("CO Main").Visible = True
          Sheets("Most States").Visible = False
      End Select
    End If
  End With
'...
End Sub

 

[Cerber1983]

Try something like this ... Alway turn on (make visible Most States). Then hide it in your special cases.

I modified your code a bit to simplify it and get rid of the IF THEN ELSEs

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Address = "$C$5" Then
      Sheets("Most States").Visible = True
      Select Case .Value
        Case "CA"
          Sheets("CA Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("CO Main").Visible = True
          Sheets("Most States").Visible = False
      End Select
    End If
  End With
'...
End Sub

Thank you very much Bosquedeguate!

It works but if I am changing from one "Special State" to another "Special State" in Cell C5 (for example: CA to WA), it does not hide CA again (need to hide CA tab and to display WA tab instead).
Note: "Most States" to hide all "Special States" and to be displayed for all other non-special states.

Below is screenshot when I am choosing 1 of special states (in this case either CA, or CO, or OH, or VA, or WA - It brings-up tab for this special state but not hiding prior ones.

 

[Bosquedeguate]

OK - similarly to always making Most State visible. hide all special states then make the one selected visible

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Address = "$C$5" Then
      Sheets("Most States").Visible = True
      Sheets("CA Main").Visible = False
      Sheets("CO Main").Visible = False
      Sheets("OH Main").Visible = False
      Sheets("VA Main").Visible = False
      Sheets("VA Main").Visible = False
      Select Case .Value
        Case "CA"
          Sheets("CA Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("CO Main").Visible = True
          Sheets("Most States").Visible = False
        Case "OH"
          Sheets("OH Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("VA Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("WA Main").Visible = True
          Sheets("Most States").Visible = False
      End Select
    End If
  End With
'...
End Sub

 

[Cerber1983]

OK - similarly to always making Most State visible. hide all special states then make the one selected visible

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Address = "$C$5" Then
      Sheets("Most States").Visible = True
      Sheets("CA Main").Visible = False
      Sheets("CO Main").Visible = False
      Sheets("OH Main").Visible = False
      Sheets("VA Main").Visible = False
      Sheets("VA Main").Visible = False
      Select Case .Value
        Case "CA"
          Sheets("CA Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("CO Main").Visible = True
          Sheets("Most States").Visible = False
        Case "OH"
          Sheets("OH Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("VA Main").Visible = True
          Sheets("Most States").Visible = False
        Case "CO"
          Sheets("WA Main").Visible = True
          Sheets("Most States").Visible = False
      End Select
    End If
  End With
'...
End Sub

It worked. Thank you very much Bosque! I really appreciate your help with that!

 

[Bosquedeguate]

By the way there is a minor error …
When I cut and pasted the Hide code for WA Main, I forgot to change “VA” to “WA”.

Please mark your Post as “Solved”.
Glad I could help.
Forrest