Run macros from drop down list

[kbattiola]

I have built a series of macros and want to be able to select from them in a drop down list. I can use a list or combo box to create the drop down list, but I am unsure how to write VBA to read the text and run the correlating macro (name of macro is identical to text shown in drop down list). Can someone provide me step by step basic instructions on how I can do this?

If this isn't possible, I would have to create a button for each of my 12 macros. How do I assign a macro to a button?

Much appreciated!

 

[klarowe]

I don't have an answer to your first question, but if you create a form button (not a control button) you can just right click on it, assign macro, then select the macro.

 

[Andrew Poulsom]

Welcome to MrExcel.

Say you use Data Validation for your dropdown in A1. Right click the sheet tab and choose View Code. Paste this into the window on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    Application.Run Target.Value
End Sub

Change the reference to A1 to suit (but make sure the $ signs remain). The macro will fire automatically when you select from the dropdown.

 

[taurean]

Welcome to the board!

While you can create such listbox, is there any particular reason to not use Excel's built in shortcut ALT + F8 which shows all (public) macros?

To use listbox you will have to put all macros in Module and then run them by using following basic syntax like:

Dim sMacro as String
sMacro = "Module1." & Listbox1.value
Application.Run sMacro