Author: Oscar Cronquist Article last updated on January 14, 2018

This article demonstrates how to execute a VBA macro using a drop down list. The drop down list contains multiple macro names.

The following animated picture shows you a drop down list containing two text strings, Macro1 and Macro2. When I select Macro1 in cell B2 a worksheet event code reads the selected value and runs a macro. The macros show a message box with the message Macro1 or Macro2 depending on the selected value.

Run a macro from a drop down list

Watch this video where I explain how to run a macro from a drop down list

https://youtu.be/21XORzFpMms

Below are detailed instructions on how I did it.

Create a drop down list

  1. Select cell B2
  2. Go to tab "Data"
  3. Click "Data validation"
    run a macro from a drop down list
  4. Select list
  5. Type your macro names in Source: field, separated by a comma.
  6. Click ok.

Recommended article

Toggle a macro on/off using a button

This article demonstrates how the user can run a macro by clicking on a button, the text on the button […]

Toggle a macro on/off using a button

Create a change event

  1. Right click on sheet name
  2. Click "View code"
    view excel sheet code
  3. The Visual Basic Editor opens. Copy vba event code below.
  4. Paste to sheet1 code module
    view excel sheet code1
  5. Exit VB Editor (Alt + Q)

Recommended article

Add your personal Excel Macros to the ribbon

If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that […]

Add your personal Excel Macros to the ribbon

VBA Event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Select Case Range("B2")
        Case "Macro1": Macro1
        Case "Macro2": Macro2
    End Select
End If
End Sub

Recommended article

How to save custom functions and macros to an Add-In

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

How to save custom functions and macros to an Add-In

Download excel *.xlsm file

Run a macro from a drop down list.xlsm