Need assistance with VBA

Cerber1983

New Member
Joined
Aug 2, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

1690977700013.png


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

1690978541635.png


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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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

VBA Code:
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
 
Upvote 1
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

VBA Code:
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.


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

Try this
VBA Code:
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
 
Upvote 1
Solution
OK - similarly to always making Most State visible. hide all special states then make the one selected visible

Try this
VBA Code:
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!
 
Upvote 0
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
 
Upvote 1

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top