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?
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) 'UpdatebyExtendoffice20180510 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 xStrNew = xStrNew & " " & Target.Value Target.Value = xStrNew 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.
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
If you are not familiar with VBA code, you can free install a 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 click Kutools > 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.|
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoIf I create a sheet with dropdown list using checkboxes, is there a way to share this workbook with this checkboxes feature?
- To post as a guest, your comment is unpublished.· 8 months agoHow would you apply this functionality but making it so that there aren't redundant values? Any help would be appreciated!
- To post as a guest, your comment is unpublished.· 9 months agoI used this "Select multiple items from drop down list to a cell without repeat"
this is what happened
1. Multiple selections happen without problems.
2. The issue is when i try to edit and remove an option. There is no way for me to remove an option. it keeps multiplying.
- To post as a guest, your comment is unpublished.· 10 months agoAfter adding,the data from dropdown,delete doesnt work.
- To post as a guest, your comment is unpublished.· 10 months agoI found the same issue. I think I found a way to overcome it. At the bottom of the code you'll see the code "xStrNew = xStrOld". Instead, make it say xStrNew = " "
It works for me.