CODEWITHSUNDEEP
vbaexcel
smurfimpulse
smurfimpulse

Reputation: 21

Drop-down list to select macro to use

I'm trying to create a drop-down in Excel where the user can select a macro from the drop down and the designed macro will run. For instance, selecting "Walmart" from the drop down will the run the Walmart designed Macro. Selecting "Sears" will run the Sears Macro. And so on. I've tried extensive research online, and tried everything from online code ranging utilization of cases to combo boxes and nothing has worked. Really stumped at this for some reason, and would appreciate any help.

Upvotes: 0

Views: 1815

Answers (1)

Chris
Chris

Reputation: 798

Here's something that I wrote recently for a similar issue. Here the dropdown is in cell A1. The macro below resides under the sheet where the dropdown is located, not a separate module. All macros that are being referred to are Public.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A1")
Application.EnableEvents = False
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    Select Case Range("M2")
        Case "Macro1": Run "Macro1"
        Case "Macro2": Run "Macro2"
        Case "Macro3": Run "Macro3"
    End Select
End If
Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions