Author: Oscar Cronquist Article last updated on November 12, 2019

Run a macro from a drop down list

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 example macros display a message box with the message Macro1 or Macro2 based 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

Below are detailed instructions on how I did it.

Create a Drop Down list

Run a macro from a drop down list arrow

A Drop Down list is a Data Validation tool that lets you control what the user can enter in a cell, however, remember that the Drop Down list is not that great in restricting what the user can enter.

For example, you can easily copy a cell and paste it to the cell containing the Drop Down list and Excel won't even warn you.

  1. Select cell B2.
  2. Go to tab "Data" on the ribbon.
  3. Click the "Data validation" button and a dialog box appears.
    run a macro from a drop down list
  4. Click on the Drop Down list below "Allow:" and select "List".
  5. Type your macro names in Source: field, separated by a comma.
  6. Click OK button.

Create a worksheet_change event

Run a macro from a drop down list event code

Event code is VBA code that is triggered if a specific thing happens like if a worksheet is selected or a cell is selected. The event code is triggered, in this example, if a cell value is changed in worksheet Sheet1.

  1. Right-click on the worksheet name located at the very bottom of your Excel screen and a menu shows up.
  2. Click "View code" on that menu, see image below, and the Visual Basic Editor opens with the worksheet module visible for Sheet1.
    view excel sheet code
  3. The Visual Basic Editor opens. Copy VBA event code below.
  4. Paste to Sheet1 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

Download Excel file

* You will also get a weekly newsletter, unsubscribe anytime!