Macro choice triggered by a drop-down list

**wolfm** · 12-05-2012, 04:33 PM

I am trying to write a macro that will:
Run the macro "Sub Emailstraightruckreq()" (or Module48) if the second value in my drop-down list in cell A28 of my current worksheet is selected.
I also want this macro to run the macro "Sub Emailshowreq()" (or Module8) if the first value in my drop-down list in cell A28 of my current worksheet is selected.

I don't know where to start, or even if you can run two macros within a macro.

I hope this makes sense??

Thank you.

**stnkynts** · 12-05-2012, 04:42 PM

One option, put in the worksheet change event. You will have to modify to meet your specific information.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet:    Set ws = Sheets("Sheet1") '<==your sheet name goes here

If Not Intersect(Target, ws.Range("A28")) Is Nothing Then
    Select Case Target.Value
        Case "test1" '<== your first value goes here
            Call Emailshowreq
        Case "test2" '<== your second value goes here
            Call Emailstraightruckreq
        'you can contine on for multiple cases if needed
    End Select
End If

End Sub

If you don't want this as a worksheet change and want it only done when you want to run it as a macro then

Sub Macro1()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1") '<==your sheet name goes here

    Select Case ws.Range("A28").Value
        Case "test1" '<== your first value goes here
            Call Emailshowreq
        Case "test2" '<== your second value goes here
            Call Emailstraightruckreq
        'you can contine on for multiple cases if needed
    End Select

End Sub

**tigeravatar** · 12-05-2012, 04:46 PM

Something like this should work. Change "First value" to be the actual first value and change "Second value" to be the actual second value. This code goes in the worksheet module of the worksheet that contains the drop-down list in cell A28

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$28" Then
        Select Case Target.Value
            Case "First Value":     Call Module48.Emailstraightruckreq
            Case "Second Value":    Call Module8.Emailshowreq
        End Select
    End If
    
End Sub

[EDIT]: Beat to it by stnkynts