VBA code optimization for data validation drop down worksheet hiding

MedDev

New Member
Joined
Aug 19, 2014
Messages
2
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

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

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
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

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

Code:
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
 
Last edited:

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
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
Code:
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

New Member
Joined
Aug 19, 2014
Messages
2
Awesome Charles!

The way you have it now works perfectly.

Highly appreciate the thought and effort put into my question.

Andy
 

Watch MrExcel Video

Forum statistics

Threads
1,095,360
Messages
5,444,015
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top