CODEWITHSUNDEEP
vbaexcel
c.p.man
c.p.man

Reputation: 15

Triggering Macro With Drop Down List

I am trying to trigger a macro with a drop down list. So far I have a working macro which selects and sorts data by column from greatest to smallest. The macro works perfectly.

Example Macro:

    Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Option+Cmd+s
'
    Range("A1:AO125").Select
    Range("A2").Activate
    ActiveWorkbook.Worksheets("Test Model").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Test Model").Sort.SortFields.Add Key:=Range( _
        "R2:R125"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Test Model").Sort
        .SetRange Range("A1:AO125")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C7").Select
End Sub

I would like to trigger this macro with a drop down list. I have created the drop down list and have written some syntax in VB editor under the excel's workbook column.

Here is the syntax so far: 

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("J15")) Is Nothing Then

    Select Case Range("J15")


        Case "Engagement Rate % ": Macro1


    End Select

End If

End Sub

When I try to run the code nothing happens... Can someone help me with my syntax?

I have added a snapshot of my screen below to help with the explaining.

Drop Down Example

Upvotes: 0

Views: 8084

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Just modify your code in Worksheet_Change event to the code below.

This will call Macro1 if the value in Cell "J15" is "Engagement Rate % " (with a space at the end ? !).

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True ' <-- just for tests
If Not Intersect(Target, Range("J15")) Is Nothing Then    
    Select Case Target.Value
        Case "Engagement Rate % "
            Macro1

    End Select    
End If

End Sub

And try this code instead of your "Macro1" (see if the MsgBox pops):

Option Explicit

Sub Macro1()
    ' Macro1 Macro
    ' Keyboard Shortcut: Option+Cmd+s

    MsgBox "Hello Test"
    'Range("A1:AO125").Select
    'Range("A2").Activate

    With Worksheets("Test Model")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("R2:R125"), SortOn:=xlSortOnValues, Order:=xlDescending, _
                            DataOption:=xlSortNormal

        With .Sort
            .SetRange Range("A1:AO125")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        '.Activate ' <-- not sure why it's needed
        '.Range("C7").Select ' <-- not sure why it's needed
    End With

End Sub

Upvotes: 1

Related Questions