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 easily and quickly
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)
'UpdatebyExtendoffice20221111
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
If Target.Value = "" 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 Sub3. 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)
'UpdatebyExtendoffice20221111
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
If Target.Value = "" 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
Both of above VBA codes not suppot to delete parts of cell contents, only support to clear all items of the cell.
Select multiple items from drop down list into a cell easily and quickly
In Excel, except VBA code, there is no other ways to allow multiple items selected from drop-down list in a cell. However, the Multi-select Drop-down List feature of Kutools for Excel can quickly and easily handle this job with clicks.
Step 1: Select Kutools > Drop-down List > Multi-select Drop-down List
Step 2: In the Multi-select Drop-down List dialog, specify the settings
- Set scope;
- Specify the separator for delimit items in a cell;
- Decide the text direction；
- Click OK.
Result:
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. |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
