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

Chlwls808

New Member
Joined
Jun 20, 2021
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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).
1.PNG


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

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 20, 2021
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • 2.PNG
    2.PNG
    70.9 KB · Views: 4

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
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:
VBA 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
 
Solution

Chlwls808

New Member
Joined
Jun 20, 2021
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
Thanks for pointing that out Joe! That fixed it!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,141,060
Messages
5,704,042
Members
421,324
Latest member
Devo182

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