Excel drop down list multiple selection without VBA

How to select multiple items from drop down list into a cell in Excel?

The drop-down list is frequently used in the Excel daily work. By default, only one item can be selected in a drop-down list. But in some times, you may need to select multiple items from the drop down list into one single cell as below screenshot shown. How can you handle it in Excel?

Select multiple items from drop down list into a cell with VBA

Select multiple items from drop down list into a cell with Kutools for Excels handy option

---

Select multiple items from drop down list into a cell with VBA

Here are some VBA can do you a favor on solving this job.

Select duplicate items from drop down list in a cell

1. After creating a drop-down list, right click at the sheet tab to select View Code from the context menu.

2. Then in the Microsoft Visual Basic for Applications window, copy and paste below code to the blank script.

VBA: Select multiple items from drop down list in a cell

Private Sub Worksheet_Change(ByVal Target As Range) 'UpdatebyExtendoffice20210409 Dim xRgVal As Range Dim xStrNew As String On Error Resume Next Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation) If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub If Intersect(Target, xRgVal) Is Nothing Then Exit Sub Application.EnableEvents = False xStrNew = Target.Value Application.Undo If xStrNew = Target.Value Then Else xStrNew = xStrNew & " " & Target.Value Target.Value = xStrNew End If Application.EnableEvents = True End Sub

3. Save the code and close the window to go back to the drop down list. Now you can select multiple items from the drop down list.

Note：

1. With the VBA, it separates the items by space, you can change xStrNew = xStrNew & " " & Target.Value to others to change the delimiter as you need. For example, xStrNew = xStrNew & "," & Target.Value will separate the items with commas.

2. This VBA code works for all drop down lists in the sheet.

Select multiple items from drop down list to a cell without repeat

If you just want to select unique items from the drop down list to a cell, you can repeat above steps and use below code.

VBA：Select multiple items from drop down list to a cell without repeat

Private Sub Worksheet_Change(ByVal Target As Range) 'UpdatebyExtendoffice20180510 Dim I As Integer Dim xRgVal As Range Dim xStrNew As String Dim xStrOld As String Dim xFlag As Boolean Dim xArr On Error Resume Next Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation) If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub If Intersect(Target, xRgVal) Is Nothing Then Exit Sub Application.EnableEvents = False xFlag = True xStrNew = " " & Target.Value & " " Application.Undo xStrOld = Target.Value If InStr(1, xStrOld, xStrNew) = 0 Then xStrNew = xStrNew & xStrOld & " " Else xStrNew = xStrOld End If Target.Value = xStrNew Application.EnableEvents = True End Sub

---

Select multiple items from drop down list into a cell with Kutools for Excels handy option

If you are not familiar with VBA code, you can free installa handy tool - Kutools for Excel, which contains a group of utilities about drop-down list, and there is an option Multi-select Drop-down list can help you easily select multiple items from drop-down list into a cell.

After creating the drop-down list, please select the drop-down list cells and click Kutools > Drop-down List > Multi-select Drop-down List to enable this utility.

Then the selected drop-down list cells can be selected multiple items into the cell.

If you use this option at first time, you can specify the settings of this utility as you need before applying this utility.

Click Kutools > Drop-down List > the arrow beside Multi-select Drop-down List > Settings.

Then in the Multi-select Drop-down List Settings dialog, you can

1) Specify the applying scope as you need;

2) Specify the direction of items place;

3) Specify the separator between items;

4) Specify if do not add duplicates and remove duplicate items.

Click Ok and clickKutools > Drop-down List > Multi-select Drop-down List to take it effect.

Tip: To apply the Multi-select Drop-down List utility, you need to install it at first. If you want to create a drop-down list with multiple levels, the below utility may help you.

Easily create a dynamic 2-level or multiple-level dependent drop-down list in Excel

In Excel, to create a 2-level or multiple level drop-down list is complex. Here the Dynamic Drop-down List utility of Kutools for Excel can do you a favor. What you only to do is order the data as example shown, then select the data range and output range, then leave it to the utility. Click for 30 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

---