How to Make Multiple Selections in Excel Drop-Down

Click here to download the featured file. (Open the workbook, ALT F11 to open the VBE and the code can be found in the relevant sheet object)

Excel now blocks macros by default when you download a workbook from the internet. Therefore none of these macros will run in the workbook I have provided unless you unblock the macros. Please see this page for guidance https://learn.microsoft.com/en-us/deployoffice/security/internet-macros-blocked

Step 1: Create a List of Values and Name It

In the video I create a list of values that I wanted to appear in the drop-down list on a separate worksheet. I also name the list.

To name a list:

1. Select the cells in the list
2. Click in the Name Box (just to the left of the formula bar – it displays the cell address of the active cell by default)
3. Type a name that starts with a letter and does not contain spaces
4. Press the Enter key on your keyboard to confirm

Step 2: Create the Drop-down List Using Data Validation

The next step is to create the drop-down list.  I did this on a separate worksheet to the list created above in step 1, although it could be created on the same worksheet. To create the drop-down list:

1. Select the cell or cells you want the drop-down list to appear in
2. Click on the Data tab on Excel’s ribbon
3. Click on the Data Validation button in the Data Tools group
4. In the Data Validation dialog, in the Allow: list select List
5. Click in the Source: box
6. Press F3 on your keyboard to open the Paste Name dialog
7. Select the name you gave your list in Step 1
8. Click on OK
9. Click OK in the Data Validation dialog

By default a drop-down list will only allow you to select one item.

STEP3: Add the VBA Code to Your Drop-Down List

To add the VBA (visual basic for applications) code to your drop-down list follow these steps:

1. Use the keyboard shortcut ALT F11 to open the Visual Basic Editor (VBE)
2. Make sure the Project Explorer is visible – it displays all the workbook’s worksheet names.  If you can’t see it, use the keyboard shortcut CTRL R to open it.
3. In the Project Explorer select the worksheet that contains your drop-down list.
4. In the Code window (that’s the big white area to the right of the Project Explorer), paste the code included in the downloadable workbook (see form above to download the workbook).
5. Close the VBE and you should find that you can select multiple items in your drop-down list.

There are a few tweaks to the code that you make and I go through these in the video.  One thing you should note is that the code, as it is, assumes you drop-down is in cell A2.  If it isn’t, change the range address in line 6.

Please note, when you save the workbook, because it now contains VBA code, it needs to be saved as a Macro-enabled Workbook (*.xlsm).

New line code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Comma-separated code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Range of cells code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2:C2")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Whole column code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Without drop-down code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
  
  If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Multi drop-down code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub