VBA & Macro Problems

Run an Excel Macro When a Drop-Down List Changes

John WilsonJohn Wilson Nov 22, 2026 868 views

Question details

Run different Excel VBA macros based on the value selected in a data-validation drop-down list.

Run an Excel Macro When a Drop-Down List Changes
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has a data-validation list, and each selection should trigger a corresponding macro.
Observed behavior
The macro should respond when the drop-down cell changes or is cleared.
Before you start

Identify the cell containing the data-validation drop-down and the exact list values and macro names you want to use. Open the workbook in desktop Excel and make sure macros are enabled for a trusted file.

Solution 1Recommended

Use Worksheet_Change to monitor the drop-down cell

Use this approach to call a different macro for each selected value in a data-validation list.

1
Open the worksheet code

In Excel, select Developer > Visual Basic. In the Project Explorer, double-click the worksheet that contains the drop-down list.

2
Add the change event

Paste this code into the worksheet's code window. Replace AN5 with your drop-down cell, and replace the option values and macro names with your own: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("AN5")) Is Nothing Then Exit Sub Select Case Me.Range("AN5").Value2 Case "Option 1" Call MacroForOption1 Case "Option 2" Call MacroForOption2 Case "" ' Add an action here if the cleared cell needs one. End Select End Sub

3
Check the called macros

In the Visual Basic editor, use Insert > Module if you need a standard module. Make sure the called macros exist there as public procedures with no required arguments, or replace the calls with your existing macro names.

4
Save and test

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Return to the worksheet, select each drop-down value, and clear the cell to test the event.

Use Worksheet_Change to monitor the drop-down cell
Clearing and recalculation: Clearing AN5 triggers the event, but the example performs no action for a blank value unless you add one. Worksheet_Change does not run when a cell changes only because a formula recalculates. Avoid having a called macro change AN5 without handling events, as that can trigger the event again.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with Microsoft Office files. Open existing spreadsheets for an easy transition; verify VBA compatibility separately before relying on Excel macros.

Create and edit spreadsheets with familiar tools, including data-validation listsOpen and work with Microsoft Excel XLSX filesUse a free, lightweight office suite for everyday document workMove between existing Office files and WPS with a familiar interface
microsoft office alternative - wps office

Frequently Asked Questions

Where should I put the Worksheet_Change code?

In Developer > Visual Basic, double-click the worksheet containing the drop-down and paste the event procedure into that worksheet's code window, not a standard module.

Why doesn't the macro run when I choose a list value?

Check that macros are enabled for the trusted workbook, the event code is in the correct worksheet, and the cell address in Me.Range matches the drop-down cell. Also confirm that the selected text matches a Case value and that Excel events are enabled.

Will the event run when I clear the drop-down cell?

Yes. Clearing the monitored cell triggers Worksheet_Change. Add instructions under Case "" if clearing it should perform an action.

Can this event detect a formula result changing?

No. Worksheet_Change does not fire solely because a formula recalculates. For that situation, consider the worksheet's Worksheet_Calculate event and check the relevant value there.

What format should I use to save the VBA code?

Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). A standard XLSX file cannot retain VBA macros.