Shakil Ahmed
Depending on the circumstances you may need to select multiple values within a drop-down list. In this tutorial, I am going to show you how to make multiple selection from a drop-down list in Excel. For this session, I am using Office 365, feel free to use your preferred version.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
3 Ideal Examples of Multiple Selection from Drop Down List in Excel
Before diving into the session, let’s get to know about the dataset that is the base of the examples. Here I have several stationery elements, using these I will create a drop down list in Excel and make multiple selection from there.
Note that it is a simple dataset to keep things straightforward. In a practical scenario, you may encounter a much larger and more complex dataset.
1. Make Multiple Selection from Drop Down List with Duplicate Options
In this example, I will explain how you can create a drop down list for multiple selections and select duplicate items. For this, you will have to create the drop down list first. But, a conventional drop down list always selects a single item. So, you will have to write a VBA code for multiple selections. Let’s explore the steps.
Step-01: Create Drop Down List in Excel
In this step, I will create the drop down list for the dataset.
- Firstly, create a section in the Excel sheet for adding the drop-down option.
- Secondly, select the cell where you want to add the drop down list. Here, I selected Cell D5.
- Thirdly, go to the Data tab.
- Next, select Data Validation.
- Next, the Data Validation dialog box will appear.
- Select the Settings tab.
- Then, click on the drop down option for Allow.
- Afterward, select List.
- Next, click on the marked button to select the source.
- After that, select the data range. Here, I selected cell range B5:B12.
- Next, click on the marked button to add the selected range as Source.
- Then, select OK.
- Finally, you will see that a drop down option is added to your selected cell.
- Now, click on the drop down button.
- Then, select an item from the list.
- After that, you will see that the selected item is showing in the selected cell.
- Next, select another item from the drop down list.
- Here, you will see that the item you selected has replaced the previously selected item.
Read More: Excel Drop Down List Depending on Selection (5 Examples)
Step-02: Write VBA Code for Multiple Selection
Now, I will show you how you can write a VBA code to make multiple selections from a drop down list in Excel.
- In the beginning, go to the Developer tab.
- Then, select Visual Basic.
- Consequently, the Visual Basic Editor will appear.
- Next, right–click on the sheet where you want to select multiple options.
- Then, select View Code.
- Now, a sheet module will appear.
- Click on the marked drop-down button.
- Then, select Worksheet.
- Afterward, select the marked drop-down button.
- Next, select Change.
- Now, you will see a Private Sub will be created.
- After that, write the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val 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
new_val = Target.Value
Application.Undo
old_val = Target.Value
If old_val = "" Then
Target.Value = new_val
Else
Target.Value = old_val & ", " & new_val
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
🔎 How Does the Code Work?
- Here, I have declared two strings called old_val and new_val.
- You can see I made the drop down list in the D5 cell, that’s why the target address is D5. And in addition, I have rechecked whether the cell is using data validation or not using Target.SpecialCells.
- Once a value is selected, I turned off events (Application.EnableEvents = False) so changes don’t trigger the event again. Then stored the selected item in the new_val.
- After undoing the change, I have set the value to the old_val. Then check whether the old_val is empty or not. If empty (means only one value is selected), then return the new_val. Otherwise, concatenate the old_val and new_val.
- Before ending the reset the event, so that I can change it if required.
- Further, Save the code and go back to your worksheet.
- Now, select a second item from the drop down list.
- Here, you will see both of the selected items are showing in the cell.
- Now, select an item that you have selected before.
- Finally, you will see that this item is also added to the list.
Read More: How to Make Multiple Dependent Drop Down List with Excel VBA
2. Select Multiple Options with Unique Selection Only from Drop Down List
In this example, I will show you how you can select multiple unique items from a drop down list in Excel. If you select an item that you have selected before then it will not add it to the Selected Item. Let’s see the steps.
Steps:
- To begin with, add a drop down list in your preferred cell by following Step-01 from Method-01.
- Next, open the Visual Basic Editor window and then open a Worksheet to write the VBA code by following Step-02 of Method-1.
- After that, write the following code in that Worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val 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
new_val = Target.Value
Application.Undo
old_val = Target.Value
If old_val = "" Then
Target.Value = new_val
Else
If InStr(1, old_val, new_val) = 0 Then
Target.Value = old_val & ", " & new_val
Else:
Target.Value = old_val
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
🔎 How Does the Code Work?
- Here, I declared 2 variables named old_val and new_val as String.
- Then, I used the Application.EnableEvents property to enable events for specified properties.
- Next, I used IF Statement to add the items from the list.
- Afterward, I used another IF Statement to check if the item was selected before.
- Then, I used the InStr function in the IF statement to find one string within another.
- Further, I ended the IF Statements.
- Finally, I ended the Sub Procedure.
- Next, Save the code and go back to your worksheet.
- Afterward, select an item from the drop down list.
- Then, you will see the item is added.
- Now, select another item from the list.
- Here, you will see both of the items are added to the Selected Item.
- Afterward, select an item that you have selected before from the drop down list.
- Now, you will see that the item is not added to the selected Item.
Read More: How to Create a Drop Down List with Unique Values in Excel (4 Methods)
Similar Readings
- How to Fill Drop-Down List Cell in Excel with Color but with No Text
- [Fixed!] Drop Down List Ignore Blank Not Working in Excel
- How to Create Dependent Drop Down List with Multiple Words in Excel
- VBA to Select Value from Drop Down List in Excel (2 Methods)
- How to Remove Used Items from Drop Down List in Excel (2 Methods)
3. Make Multiple Selection and Add Items on New Line in Excel
So far, I have found the items are separated by a comma. In this section, I will arrange the selected items in newlines. Let me show you the steps.
Steps:
- In the beginning, I will merge a few cells with Cell D5.
- To do that, select the cells you want to merge. Here, I selected cell range D5:D9.
- Then, go to the Home tab.
- After that, select Merge & Center.
- After that, add the drop down list following Step-01 from Method-1.
- Afterward, open the Visual Basic Editor window and then open a worksheet to write the VBA code by following Step-02 of Method-1.
- Then, write the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val 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
new_val = Target.Value
Application.Undo
old_val = Target.Value
If old_val = "" Then
Target.Value = new_val
Else
If InStr(1, old_val, new_val) = 0 Then
Target.Value = old_val & vbNewLine & new_val
Else:
Target.Value = old_val
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
- After that, Save the code and go back to your worksheet.
- Next, select an item from the drop down list.
- Then, you will see that the item will be added to the Selected Item.
- After that, select another item from the drop down list.
- Now, you will see that the new item is added to a new line.
- In the following image, you can see that I have added a few more items from the list and each of them is added to a new line.
Read More: How to Add Item to Drop-Down List in Excel (5 Methods)
Things to Remember
- If you are working with VBA then you must save the Excel file as Excel Macro–Enabled Workbook.
Practice Section
Here, I have provided a practice sheet for you to practice how you can create a drop down list with multiple selection options in Excel.
Conclusion
That’s all for today. We have listed several approaches to make multiple selection in a drop-down list in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here.
Related Articles
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
- How to Create a Form with Drop Down List in Excel
- Creating a Drop Down Filter to Extract Data Based on Selection in Excel
- Excel Drop Down List Not Working (8 Issues and Solutions)
- How to Create Multi Select ListBox in Excel (With Easy Steps)
- Excel Formula Based on Drop-Down List (6 Suitable Examples)
How can you apply this formula to a range of cells? I would like this to apply to a column of cells instead of a single cell.
If you want to traverse through a column, you can wrap your code with loop which will continue throughout the column.
Same question here!
Hi! I have the same question!
Hi! Same question! Anybody find an answer?
You can wrap your code in a loop to iterate continuously across a column if you want to traverse it.
Hi, this was really helpful but please could you provide the code for the loop? Thank you!!
I have tried the following VBA code for all the cells in D Column. I hope this is the thing you are looking for.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Dim mn As Range, pq As Range
On Error GoTo Exitsub
Set mn = Range(“D:D”)
For Each pq In mn
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
Next pq
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
I deleted the following lines of the VBA code:
If Target.Address = “$D$4” Then
the first “End If”
Thanks for your insight and for trying something on your own.
How do you deselect once selected using this code?
You can use the Clear property of the cell. You need to apply Range(“__your cell__”).Clear to make the selection cleared.
I would like to know this, too
Its brilliant, but………. I want to also be able to add freeform text. So user selects a couple of standard texts from the drop down – all good
Then they click in to add specific text, hit enter and all of a sudden it duplicates the specific text already added.
I cant quite work out why this is happening.
I’m afraid in an ideal scenario once you’ve used data validation with list of items to be selected, you will encounter an error since Excel anticipates values from that list only not any free text.
Thanks all of you guys for your comments.
I think so many people are facing the same problem of applying the code in a range of cells. In our article, the VBA code that we have shown only works for a fixed cell. So, I am going to give you guys a slightly modified VBA code that will work for a range of cells( i.e. entire D column).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Dim mn As Range, pq As Range
On Error GoTo Exitsub
Set mn = Range(“D:D”)
For Each pq In mn
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
Next pq
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Hello,
that’s really interesting, but using this last code I get an error of syntax related to the following lines:
Set mn = Range(“C:C”)
…
Target.Value = Oldvalue & “, ” & Newvalue
Where is this syntax error?
Thanks
Thanks for the appreciation.
In my case, it works just fine. It is very tough for me to give a solution without analyzing your code related to the dataset. It would be helpful for me if you could provide me your code.
Dear LUIS FERNANDO,
Thanks for the appreciation. Also big thanks to you for sharing your insights on the query of CHRISTOFER.
In response to your purpose, I have considered a case where I will have a drop-down with the names of the writers. Based on the writer selection, his books will appear in another drop-down just below the cell of the writer’s name.
I have assigned the following code in a button where I will have writers’ names without repetition under Uniquelist column. I have created a drop-down in cell C14 with those values. Similarly, I have sorted the matched books under Sorted Books column with the writer’s name in C14.
Now, select a writer name from drop-down and click on Sort button. You will have the related books’ name in the following drop-down and choose a book according to your preference.
They are getting the error from your code because the quotation marks need to be changed to straight quotation marks ” ” but when they paste your code any place where there are marks come up as slanted quotation marks “ ”, and the vba code doesn’t recognize it.
Your code is helpful but for my purposes it still doesnt provide the necessary solution. I need the additional selected options to appear in rows below the row of the cell being used with the DV.
I am now getting the same code as Christopher.
Syntax error in two lines of code:
Set mn = Range(“G:G”)
and
Target.Value = Oldvalue & “;” & Newvalue
I copied your code exactly, and only changed it to G:G to relate to my specific column needs.
Many thanks,
Thank you Naimul,
this is the code, I didn’t report it because I just copied and pasted your.
I only changed the column from D to C:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Dim mn As Range, pq As Range
On Error GoTo Exitsub
Set mn = Range(“C:C”)
For Each pq In mn
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
Next pq
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Thanks for your help! I am trying to use your code, but receive an error stating “Compile Error; Expected: End of statement” with the first Dim highlighted. What does that mean?
Hello ANN HALL,
This may happen for different reasons.
1. Putting two statements in a single line. Check whether this is encountered and send the second statement in a new line.
2. Absence of parentheses.
3. Lack of whitespace in front of the ampersand(&) interpret as a long variable. So, put space between the variable and operator(&).
Hello – is there a way to turn off the error checking in the excel cell. I am creating a form for users and I don’t want them to see the “validation error” message.
Hello SG,
You can customize the error message. In the Data Validation message box=> Error Alert icon, you can choose “Warning” style from the dropdown list. Now, if you try to input invalid data, the error message will show 3 options asking you whether you want to continue: Yes/No/Cancel/Help. Clicking “Yes” will allow you to proceed with the current value and will not show the error again.
Hello, I tried this however absolutely nothing seems to happen. The first time I attempted to click ”save” it asked me to save the spreadsheet in a macro enabled format (.xlsm) which I have done. I closed it and reopened it. But nada! The code is in there but it doesnt seem to have worked.
I literally copied/pasted the code exactly as you wrote it….assuming nothing needed to be amended in the code?
Thanks,
Thanks for your valuable comment.
A few factors might play a vital role in your problem.
1. As the file contains VBA code, the file must be saved in “.xlsm” format.
2. Don’t forget to change the ranges in your code. I have applied the code in D column. So, it’ll only be applicable in the D column.
Hi Naimul Hasan Arif – thanks for your patience in responding to everyone’s queries and issues :). Not sure if it will work for me, yet, but appreciate your efforts.
Hello, Helen!
Thanks for your appreciation. To get more helpful information stay in touch with ExcelDemy.
Regards
ExcelDemy
I have different lists in a single sheet where the VBA code is to be placed. Now I have to make multi selections from specific lists (ie having cells referring to different list to make multi selection from). Should I simply add different data validation for the respective cells?
Dear PRATIK,
Thanks very much for reading our articles. You have mentioned that you have different lists in a single sheet. Here, we will show how to declare data validation in different cells with different lists in a single sheet.
Steps:
● Here, we have three different lists of mobile, laptop, and routers. Here, we will introduce different drop-down lists with multiple selections.
● Now, apply data validation as shown in the article already.
● We will choose Range B5:B11 at Cell G4, Range C5:C11 at Cell G5, and Range D5:D11 at Cell G6 for the data-validation source.
● Choose any item from the drop-down list of Cell G4.
● Similarly, choose items for Cell G5 and G6 from the drop-down list.
● Now, put the following VBA code in the VBA module. After that, save the VBA code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Column = 7 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
● Now, choose multiple options from the drop-down list.
Here, we changed the Target.Address to Target.Column and put the column number. Also, need to mention the whole column will save the previous result.
Please Help. The First code works for my project however I can only get the drop down list to work for once cell.
I want to change the line
If Target.Address = “$AG$4” Then
to include cells AG4 to AG43
How do I get this to work? I tried $AG$4:$AG$43, as well as AND but they did not work
Hello Allison,
Glad that you shared your problem. I have looked at it and therefore suggest these solutions.
If you want a drop-down list for some selected cells (AG4:AG43) then replace
If Target.Address = "$AG$4" ThenWith
If Target.Address = "$AG$4" Or Target.Address = "$AG$5" Or Target.Address = "$AG$6" …….. Or Target.Address = "$AG$43" ThenIf you want a drop-down list for the whole AG Column which is the 33rd column in the workbook then replace
If Target.Address = "$AG$4" ThenWith
If Target.Column = 33 ThenI hope the solutions will help you. Let us know your feedback.
Regards,
Guria,
Exceldemy
@ Allison, try this one:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range(“F:F”)) 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
This is VERY helpful and just what I need except…I need the selected item to generate a total cost. Each item has a cost so how would that be totaled after selection? I would be very grateful for your help as I am stuck on this for a simple project. Thank you!
Hello Winsett, thanks for reaching out. In that case, use SUM function to calculate the total cost. Also, you can find the total cost automatically in the bottom right corner of your Excel sheet for the selected data. To sum the cost by criteria, use the SUMIF/SUMIFS function.
I would like to be able to edit the list but when I delete an entry, I get a validation error.
Please mention which method you are following, this will help us to understand the reason why this error is occurring. Thank you.
Use author’s second method and fill in the range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Dim mn As Range, pq As Range
Application.EnableEvents = True
On Error GoTo Exitsub
Set mn = Range(“CC:CC”)
For Each pq In mn
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
new_val = Target.Value
Application.Undo
old_val = Target.Value
If old_val = “” Then
Target.Value = new_val
Else
If InStr(1, old_val, new_val) = 0 Then
Target.Value = old_val & “, ” & new_val
Else:
Target.Value = old_val
End If
End If
End If
Next pq
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub