CODEWITHSUNDEEP
vbaexcelevents
Joseph Lin
Joseph Lin

Reputation: 79

Run Event Macro After Data Validation Selection

I built up a data validation list on the sheet called report, I need to run a macro each time I select an item from the list. Now that I have this code below, but it doesn't work. It does run in VBA, but it will not run when I choose the Item in my worksheet, it seems like I didn't refer to the macros

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address(True, True) = "$B$3" Then
        Select Case Target
             Case "ABCP"
                Call Macro1
             Case "Accounting Policy"
                Call Macro2
             Case Else
                  'Do Nothing
        End Select
    End If
End Sub

Upvotes: 1

Views: 12431

Answers (2)

ChipperG
ChipperG

Reputation: 64

The above code appears to work fine for me.

Have you placed the code in the worksheet code? as opposed to a Module?

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

If you want to run the procedure when you have changed the value in B3 (picking from the data validation list, then you want a Worksheet_Change event macro, not a Worksheet_SelectionChange.

Additionally, if anything on the worksheet is going to change then you will want to disable events so the event macro does not attempt to run on top of itself.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$3" Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Select Case Target.Value2
             Case "ABCP"
                Call Macro1
             Case "Accounting Policy"
                Call Macro2
             Case Else
                  'Do Nothing
        End Select
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

This should launch the correct sub procedure when B3 has a new value selected from the list. You would have to provide more details (and code) for m Macro1 and Macro2 if it continues to be problematic.

Upvotes: 5

Related Questions