Run an Excel Macro When a Drop-Down List Changes
Question details
Run different Excel VBA macros based on the value selected in a data-validation drop-down list.

- 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.
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.
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.
In Excel, select Developer > Visual Basic. In the Project Explorer, double-click the worksheet that contains the drop-down list.
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
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.
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.

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.

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.




