Activate Worksheet If certain value entered in specific cell

MrJohnny

New Member
Joined
Jul 17, 2012
Messages
2
Good day,
I got a sheet that users will select an origin country in a dropdown (cell D5) and a destination country (cell E5).
When they complete their selection, a concatenated value of these two choices will appear in H5.
Depending on the value that appears, a specific worksheet needs to be activated.

Here's what i have so far, the concatenate is working fine, but i cannot figure out how to have the value in H5 be the key that make the worksheet activate.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, lngRow As Long
    If Not Intersect(Target, Range("D5:E5")) Is Nothing Then
   On Error Resume Next
   Application.EnableEvents = False
   For Each rngCell In Intersect(Target, Range("D5:E5"))
      lngRow = rngCell.Row
      Cells(lngRow, "H").Formula = Cells(lngRow, "D").Value & Cells(lngRow, "E").Value
   Next rngCell
   Application.EnableEvents = True
     
 
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "Selection" Then ws.Visible = xlSheetVeryHidden
Next
    Select Case Target.Value
        Case "USAChina"
            Worksheets("USAChina").Visible = xlSheetVisible
        Case "USABelgium"
            Worksheets("UStoBE").Visible = xlSheetVisible
        Case "USAFrance"
            Worksheets("UStoFR").Visible = xlSheetVisible
        Case "USGermany"
            Worksheets("UStoDE").Visible = xlSheetVisible
        Case "USAGreece"
            Worksheets("UStoGR").Visible = xlSheetVisible
        Case "USAIndonesia"
            Worksheets("UStoID").Visible = xlSheetVisible
        Case "USAIreland"
            Worksheets("UStoIE").Visible = xlSheetVisible
        Case "USAIsrael"
            Worksheets("UStoIL").Visible = xlSheetVisible
        Case "USAItaly"
            Worksheets("UStoIT").Visible = xlSheetVisible
        Case "USA***an"
            Worksheets("UStoJP").Visible = xlSheetVisible
        Case "USAKorea"
            Worksheets("UStoKR").Visible = xlSheetVisible
        Case "USAMalaysia"
            Worksheets("UStoMY").Visible = xlSheetVisible
        Case "USANetherland"
            Worksheets("UStoNL").Visible = xlSheetVisible
        Case "USAPakistan"
            Worksheets("UStoPK").Visible = xlSheetVisible
        Case "USAPhillipines"
            Worksheets("UStoPH").Visible = xlSheetVisible
        Case "USAPortugal"
            Worksheets("UStoPT").Visible = xlSheetVisible
        Case "USASouth Africa"
            Worksheets("UStoZA").Visible = xlSheetVisible
        Case "USASpain"
            Worksheets("UStoES").Visible = xlSheetVisible
        Case "USASweden"
            Worksheets("UStoSE").Visible = xlSheetVisible
        Case "USATaiwan"
            Worksheets("UStoTW").Visible = xlSheetVisible
        Case "USAThailand"
            Worksheets("UStoTH").Visible = xlSheetVisible
        Case "USATurkey"
            Worksheets("UStoTK").Visible = xlSheetVisible
        Case "USAUnited Kingdom"
            Worksheets("UStoUK").Visible = xlSheetVisible
        Case "USAViet Nam"
            Worksheets("UStoVN").Visible = xlSheetVisible
        End Select
      End If
End Sub

Thank you for your help!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

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
Top