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

This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two macro names Macro1 and Macro 2, however, you can easily add more if you like.

The animated picture below shows 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

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.

Where to put the event code?

Event code is VBA code that is triggered if a specific thing happens, for example, if a worksheet is selected or a cell is selected, however, there are many many more. The event code is triggered, in this example, if a cell value is changed in worksheet Sheet1.

Remember that the worksheet_change event works only if you put it in a worksheet module, here is how to access a worksheet module:

  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 worksheet module.
    view excel sheet code1
  5. Exit VB Editor (Alt + Q).
Note, save the workbook with file extension *.xlsm (macro enabled) to attach the code to your workbook. This will save the workbook and the code so you can use it the next you open the workbook.

VBA Event code

'Event code, Target variable contains the cell or range that has been changed
Private Sub Worksheet_Change(ByVal Target As Range)

'Interesect method returns a range object of a rectangular intersection of two or more cell ranges
If Not Intersect(Target, Range("B2")) Is Nothing Then

    'Select Case statment performs one out of several groups of statements based on a condition
    Select Case Range("B2")

        'If text in cell B2 is equal to Macro1 a macro named Macro1 is executed
        Case "Macro1": Macro1

        'If text in cell B2 is equal to Macro2 a macro named Macro2 is executed
        Case "Macro2": Macro2

    'Stops Select Case statement
    End Select
End If
End Sub

Here is the first macro named Macro1, it shows a message box with text "Macro1".

Sub Macro1()
MsgBox "Macro1"
End Sub

The second macro named Macro2 shows a message box with text "Macro2".

Sub Macro2()
MsgBox "Macro2"
End Sub

These macros are located in a code module that you need to insert yourself.

Where to put macros?

  1. Press keyboard shortcut keys Alt + F11 to open the Visual Basic Editor.
  2. Click "Insert" on the menu, see image above.
  3. Click "Module" to insert a module to your workbook.
  4. Copy and paste macros to code window.
  5. Exit VB Editor and return to Excel.

Save your workbook with file extension *.xlsm.

What happens if the Drop Down list is overwritten?

As I mentioned at the beginning of this article Drop Down lists have a major disadvantage, it can easily be overwritten.

For example, copy another cell and paste it to the cell containing a Drop Down list and the Drop Down list is now gone without a warning.

The event code is not even returning an error message, you can, however, add an extra line to the event code that takes care of invalid values.

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
        Case Else: MsgBox "Macro not available"
    End Select
End If
End Sub

This will not prevent the Drop Down list from being overwritten, it will only return a message telling the Excel user that the value in cell B2 is not valid.

To prevent it from being overwritten you can use the code I found here: Restrict paste into dropdown cells