How to run a macro when Dropdown List value is selected

You can easily add a Dropdown List in your Excel worksheet using Data Validation option. A drop down list stores multiple values placed inside a cell. You can click the drop down list arrow and select a value. That's it. But, do you know you can run a macro, or call a VBA function, after selecting a value from the Drop Down list? There's a simple trick and I'll show you how.

The VBA code

A Dropdown List in Excel is slightly different from a ComboBox in Excel. A ComboBox is an ActiveX control and has events (like the Change event). However capturing Excel's dropdown list value using VBA, is different.

Here's an example.

Private Sub Worksheet_Change(ByVal Target As Range)
    splitText (Target)
End Sub

Sub splitText(ByVal sBirds As String)
   
    Dim str() As String

    If Len(sBirds) Then
        str = VBA.Split(ActiveCell.Value, vbLf)
        ActiveCell.Resize(1, UBound(str) + 1).Offset(0, 1) = str
    End If
End Sub

See the "Target" (its a range) can be any control. In our case its a drop down list.

You can use the value property too (be more clear). Like this...

Debug.Print (Target.Value)

run a macro when dropdown list value is selected in excel

← PreviousNext →