VBA code optimization for data validation drop down worksheet hiding

[MedDev]

Hi,
I have about fifty worksheets most of which are named with two letter country codes.
I am using a data validation drop down list to select the relevant country code (US, CA, EU etc…) worksheet in order to hide all the other country worksheets. However, I don’t want to hide all the worksheets, just the ones with a two letter country codes that isn't specified by the data validation dropdown. I have the following code that works save for the fact it is too long and can’t include all the countries without calling from a separate module. I am new to VBA and have looked at similar questions and it seems that is a way to optimize this code so that it is much smaller so that when I select a country from the data validation drop down all other two country code names worksheets are hidden? Any help you can provide is much appreciated.

Thanks
Andy

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("marketSelect").Address Then
    If Target.Value = "Canada" Then
        Sheets("US").Visible = False
        Sheets("CA").Visible = True
        Sheets("EU").Visible = False
        Sheets("AE").Visible = False
        Sheets("AR").Visible = False
        Sheets("AU").Visible = False
        Sheets("BH").Visible = False
        Sheets("BM").Visible = False
        Sheets("BR").Visible = False
        Sheets("BY").Visible = False
        Sheets("GB").Visible = False
        Sheets("CH").Visible = False
        Sheets("CL").Visible = False
        Sheets("CN").Visible = False
        Sheets("CO").Visible = False
        Sheets("CR").Visible = False
        Sheets("EC").Visible = False
        Sheets("HK").Visible = False
        Sheets("ID").Visible = False
        Sheets("IL").Visible = False
        Sheets("IN").Visible = False
        Sheets("IT").Visible = False
        Sheets("JM").Visible = False
        Sheets("JO").Visible = False
        Sheets("JP").Visible = False
        Sheets("KE").Visible = False
        Sheets("KR").Visible = False
        Sheets("KW").Visible = False
        Sheets("LB").Visible = False
        Sheets("LK").Visible = False
        Sheets("MX").Visible = False
        Sheets("MY").Visible = False
        Sheets("NO").Visible = False
        Sheets("NZ").Visible = False
        Sheets("PH").Visible = False
        Sheets("RU").Visible = False
        Sheets("SA").Visible = False
        Sheets("SG").Visible = False
        Sheets("TH").Visible = False
        Sheets("TR").Visible = False
        Sheets("TW").Visible = False
        Sheets("UA").Visible = False
        Sheets("UY").Visible = False
        Sheets("VE").Visible = False
        Sheets("VN").Visible = False
        Sheets("ZA").Visible = False
    ElseIf Target.Value = "USA" Then
        Sheets("US").Visible = True
        Sheets("CA").Visible = False
        Sheets("EU").Visible = False
        Sheets("AE").Visible = False
        Sheets("AR").Visible = False
        Sheets("AU").Visible = False
        Sheets("BH").Visible = False
        Sheets("BM").Visible = False
        Sheets("BR").Visible = False
        Sheets("BY").Visible = False
        Sheets("GB").Visible = False
        Sheets("CH").Visible = False
        Sheets("CL").Visible = False
        Sheets("CN").Visible = False
        Sheets("CO").Visible = False
        Sheets("CR").Visible = False
        Sheets("EC").Visible = False
        Sheets("HK").Visible = False
        Sheets("ID").Visible = False
        Sheets("IL").Visible = False
        Sheets("IN").Visible = False
        Sheets("IT").Visible = False
        Sheets("JM").Visible = False
        Sheets("JO").Visible = False
        Sheets("JP").Visible = False
        Sheets("KE").Visible = False
        Sheets("KR").Visible = False
        Sheets("KW").Visible = False
        Sheets("LB").Visible = False
        Sheets("LK").Visible = False
        Sheets("MX").Visible = False
        Sheets("MY").Visible = False
        Sheets("NO").Visible = False
        Sheets("NZ").Visible = False
        Sheets("PH").Visible = False
        Sheets("RU").Visible = False
        Sheets("SA").Visible = False
        Sheets("SG").Visible = False
        Sheets("TH").Visible = False
        Sheets("TR").Visible = False
        Sheets("TW").Visible = False
        Sheets("UA").Visible = False
        Sheets("UY").Visible = False
        Sheets("VE").Visible = False
        Sheets("VN").Visible = False
        Sheets("ZA").Visible = False
    ElseIf Target.Value = "European Union" Then
        Sheets("CA").Visible = False

 

[CharlesChuckieCharles]

There are two stages

a new sub to do the donkey work

Note the len=2 condition is not required if it is not a true filter

Sub HideUnwantedSheets(bHide As Boolean, list As Variant)

Dim Mysheet As Worksheet
      
    For Each Mysheet In ActiveWorkbook.Sheets
       
       IF bhide=true then

       If Len(Trim(Mysheet.Name)) = 2 Then
         If InStr(1, UCase(list), UCase(Trim(Mysheet.Name))) = 0 Then
            Mysheet.Visible = xlSheetHidden
         Else
            Mysheet.Visible = xlSheetVisible
         End If
       End If
       else

      Mysheet.Visible = xlSheetVisible

      endif

       
       DoEvents
    
    Next Mysheet
    
 
 
End Sub

then change your event code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("marketSelect").Address Then
    Select Case Target.Value
     Case "Canada"
        mystr = "CA"
     Case "USA"
        mystr = "US"
     Case "European Union"
        mystr = "EU"
     Case "European Union"  'proper way
        mystr = "GB_FR_DE_IT"  'this will unhide four sheets
     Case Else
        mystr = "NONE"
      End Select  

Call HideUnwantedSheets(True, mystr)

End If
End Sub

Call HideUnwantedSheets(False, "") will un-hide everything

 

[CharlesChuckieCharles]

you should set up a lookup in the sheet with the data validation


Canada CA

USA US

European Union EU (or GB_FR_DE_IT_CZ etc all the different countries in a single string)

India IN

The change your event code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("marketSelect").Address Then
    mystr = Application.WorksheetFunction.VLookup(Target.Value, Range("$K$1:$L$38"), 2, False)
    Call HideUnwantedSheets(True, mystr)
End If
End Sub

 

[MedDev]

Awesome Charles!

The way you have it now works perfectly.

Highly appreciate the thought and effort put into my question.

Andy