ThisWorkbook code to operate macro using drop down selection doesn't work

[Chlwls808]

hi,
I have a drop down selection of the items listed on A1:A6 like below. The point of this is to execute a macro based on the items selected (MsgBox will appear for each items selected).

I also have a code saved in 'ThisWorkbook' as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Select Case Range("B2")
            Case "Hurricane"
                MsgBox "This is Hurricane!"
            Case "Earthquake"
                MsgBox "This is Earthquake!"
            Case "Typhoon"
                MsgBox "This is Typhoon!"
            Case "Tsunami"
                MsgBox "This is Tsunami!"
            Case "Tornado"
                MsgBox "This is Tornado!"
            Case "Blizzard"
                MsgBox "This is Blizzard!"
        End Select
    End If
End Sub

However, when I go back and make my selection, the MsgBox doesn't pop up. I tried testing the MsgBox capabilities by inserting the code in Module and it works fine over there. What am I doing wrong?

Thank you.

 

[Joe4]

I also have a code saved in 'ThisWorkbook' as follows:

I think you are using the wrong module/procedure.

Go to the sheet module that this exists on, and put the code in a "Worksheet_Change" procedure instead.

 

[Chlwls808]

I think you are using the wrong module/procedure.

Go to the sheet module that this exists on, and put the code in a "Worksheet_Change" procedure instead.

Thanks for your response. I tried adding it to the Worksheet this is under as per your suggestion, but I still don't see any results.

 

[Joe4]

You code is specifically looking only at cell B2, and your entry is in cell B1.

If you want to apply that logic to any entry in column B, try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("B:B"))
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        Select Case cell
            Case "Hurricane"
                MsgBox "This is Hurricane!"
            Case "Earthquake"
                MsgBox "This is Earthquake!"
            Case "Typhoon"
                MsgBox "This is Typhoon!"
            Case "Tsunami"
                MsgBox "This is Tsunami!"
            Case "Tornado"
                MsgBox "This is Tornado!"
            Case "Blizzard"
                MsgBox "This is Blizzard!"
        End Select
    Next cell

End Sub

 

[Chlwls808]

Thanks for pointing that out Joe! That fixed it!

 

[Joe4]

You are welcome.