Multi-Select Drop Down Excel VBA Macro

In a previous post we saw how to add a drop-down list to selected cells with Excel VBA. Also known as "data validation", that feature in Excel allows selecting only one value from the drop down at a time. In this post we see how to let users pick multiple values from a drop down, and display them all in the cell. Selected values can be separated by a comma or any other character, or even appear one below the other (line break) inside the cell. 

Macro/VBA code:

   

  'In sheet module

  Private Sub Worksheet_Change(ByVal Target As Range) 

      If Target.Address = "$D$3" Then Call AddDropDownValues(Target)  'UPDATE target cell!

  End Sub

  

  'In standard module

  Sub AddDropDownValues(rng As Range)

      Dim newVal As String, oldVal As String

      newVal = rng.Value

      If newVal <> "" Then

          Application.EnableEvents = False

          Application.Undo

          oldVal = rng.Value

  

          If oldVal <> "" Then

              rng.Value = oldVal & ", " & newVal  'or vbNewLine

          Else

              rng.Value = newVal

          End If

  

          Application.EnableEvents = True

      End If

  End Sub

   

Macro explained:

• The macro consists of two procedures: a worksheet change event procedure in the sheet module of the sheet with the target cell, and another procedure in a standard module that adds the selected values to the cell.
• The worksheet change event procedure triggers when a value in the target cell is selected (cell D3 in the example macro above, change as needed). When triggered, it calls the other procedure and passes the target cell as a Range object.
• The main procedure (AddDropDownValues) accepts a Range object - the target cell, and adds the selected value to whatever is already in the cell.
• The two string variables (oldVal and newVal) store the old and new values in the cell.
• The variable newVal stores the last selected value from the drop down.
• Then, the macro reverts the previous value using the Undo method of the Application object. Note that Application.EnableEvents must be set to false before doing that.
• Now the variable oldVal stores the previous values (if any).
• Finally, the value in the cell is set to the previous values (oldVale) along the last value selected (newVal) separated by a comma or any other character. It can also put each value in a new line using vbNewLine instead of the character. In such case, the cell height needs to be resized to let all values fit.
• The conditional statement checks whether a previous value exists or not. If the cell was empty, a unique value is added (newVal). This happens when the very first value is selected.
• Note that Application.EnableEvents is set back to true at the end of the macro.

This is how we add a multi-select drop down in Excel with VBA macros.

Other examples:

• How to Get All Combinations From Array with Excel VBA macros
• How to Add Drop-Down List To Selection with Excel VBA macros
• How to Add Checkboxes To Selection with Excel VBA macros