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) '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 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) '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.
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.
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!