Rifat Hassan
Until now, we looked at how to create drop-down lists in Excel. Today I will show how to create a drop-down list with multiple selections in Excel.
Download Practice Workbook
You can download the practice workbook from here.
Step-by-Step Procedures to Create Drop Down List in Excel with Multiple Selections
Here, we’ve got a dataset having a column Book Name which contains some book names. Our objective today is to create a drop-down list based on this dataset that takes multiple selections. I will show step-by-step procedures in the below section.
STEP 1: Create Drop-Down List by Using Data Validation
For creating a drop-down list with multiple selections, we have to create a drop-down list first. Let’s walk through the procedures.
- First, select the cell where you want to create the drop-down list. I have selected Cell D5.
- Next, go to the Data tab and select Data Validation from the ribbon.
- Then, from the Data Validation window, select List in Allow section and write the range cells whose data you want to add to the list in the Source field.
- Alternatively, you can click on the small upward arrow in the Source section and select the data range from the worksheet.
- Finally, we will see a drop-down list created in Cell D5.
Similar Readings:
- How to Make a Drop Down List in Excel (Independent and Dependent)
- Make Multiple Selection from Drop Down List in Excel (3 Ways)
- How to Create Dependent Drop Down List in Excel
- Create Drop Down List in Multiple Columns in Excel (3 Ways)
STEP 2: Enabling Drop-Down List to Accept Multiple Selection by VBA Code
We have created the drop-down list already. Now, it’s time to prepare the drop-down list for multiple selections. I will use 2 VBA codes to enable the list to accept multiple selections. One will accept the repetition of data and another one won’t take the repetition of data.
Case 1: VBA Code for Multiple Selections with Repetition
In this section, I will show the way to create a drop-down list with multiple selections which will take a repetition of data.
Let’s walk through the procedures.
- First, press ALT + F11 to open the VBA window.
- Then, select Project Explorer. Also, double–click on the sheet where you want the task to be done.
- Simultaneously, a Code window will open.
- Afterward, Write the following code in that window.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$D$5" 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
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
- Finally, come back to the worksheet and we will be able to select multiple elements in the drop-down list with a repetition of the same element.
Case 2: VBA Code for Multiple Selection without Repetition
In this section, I will show the way to create a drop-down list with multiple selections which won’t take a repetition of data.
Let’s walk through the procedures.
- First, press ALT + F11 to open the VBA window.
- Then, select Project Explorer. Also, double–click on the sheet where you want the task to be done.
- Simultaneously, a Code window will appear.
- Afterward, Type the following code in that window.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$5" 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
- Finally, come back to the worksheet and we will be able to select multiple elements in the drop-down list without a repetition of the same element.
Conclusion
Using this method, you can create a drop-down list in Excel with multiple selections. Do you have any questions? Feel free to let us know in the comment section. Visit our ExcelDemy Website for more articles regarding Excel.
When you create a list that allows for multiple selections in one cell, how does this impact the item counts on your pivot table? Is each selection within the cell counted as a singular item? Or are all of the values in the cell counted as one item?
Hlw Kim, Sorry for being late.
# Excel Pivot Table normally considers multiple items within a cell as “1”. Therefore, multiple selection within a cell doesn’t have any effect on count. However, conditional counting may have different issue.
How would you duplicate this to have more than 1 multiple select list option in the same worksheet?
Hlw Avery, Sorry for late reply.
To duplicate this for more than 1 multiple select list option:
# Replace ” If Target.Address = “$D$4″ Then ” line within the macro (with repetition or without repetition) with ” If Not Intersect(Target, Range(“D4:D5”)) Is Nothing Then “.
Note: You can have multiple Source ranges assigned for “Target.Address” s using Data Validation.
How to apply the macro to all cells in the same column?
Hlw Reena Pujari. Sorry for late reply.
To apply macro to all cells in the same column, follow:
1. Apply “Data Validation” to all the cell of a column (Highlight Entire Column > go to “Data” > apply “Data Validation”)
2. Replace ” If Target.Address = “$D$4″ Then ” line within the macro (with repetition or without repetition) with ” If Not Intersect(Target, Range(“D:D”)) Is Nothing Then “.
Hello,
I need to do data validation, with multiple select and no repetition, on two columns starting in row 7. I have modified the code as follows am receiving a validation error on the replaced line of code.
Private Sub Worksheet_Change(ByVal Target As Range)
‘ To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range(“g:g”)) 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
Can you help us fix the code?
Hlw Melissa,
I’m assuming you need to Data Validate multiple selection G7 to H15 (adjacent columns) or whatever the cell is.
# Modify the 1st IF Condition as ” If Not Intersect(Target, Range(“G7:H15″)) Is Nothing Then ” or Change the ” Range(“G7:H15″) ” portion with Capital Letters. Of course don’t forget to apply Data Validation to those cells first.
Hope this works. Otherwise, comment with detailed cell references.
Regards
Maruf
How can I apply the Multi validation to the entire sheet? what change I need to make in the code?
How can I apply the Multi validation to the entire sheet and not any individual column
Hi JUNED SHAIKH
I hope you are doing well. The term “Multi Validation” is a bit confusing. However, I would like to provide you with some information regarding Data Validation in Excel.
1. You cannot have multiple columns or rows as your data source for data validation. The data source must be a single row or column.
2. You can apply data validation to one specific cell once at a time. If you want to reapply data validation to one particular cell, you have to remove the previous data validation.
3. You can apply data validation to the entire sheet keeping the procedure congruent with the above statements.
I hope it helps. Have a great day.
Hi, your code is great but the replacement line to allow for multiple selection isnt working for me. i think the error is with the range colon. its not accepting this:
Range(“B4:B40”))
keeps highlighting the colon as the issue.
any ideas?
thanks
I needed to apply the drop list with multiple selections to a specific column (not just a cell) and add a new line after each selection.
To apply drop list with multiple selections to a single column:
Replace ‘” If Target.Address = “$D$5″ Then ” with ” If Target.Column = 10 Then ” (my list is in column 10)
To add a new line after each selection:
Replace ” Target.Value = Oldvalue & “, ” & Newvalue ” with ” Target.Value = Oldvalue & vbNewLine & Newvalue ”
Hope this helps anyone who may have come across the same issue.
Thanks so much to the OP! This has helped me tremendously 🙂
Hello Zuri,
Thanks for your suggestion.
Regards
Shamima Sultana | Project Manager | ExcelDemy