One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel.
When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.
He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.
Something as shown below in the pic:
There is no way you can do this with Excel in-built features.
The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value.
Watch Video – How to Select Multiple Items from an Excel Drop Down List
How to make Multiple Selections in a Drop Down List
In this tutorial, I will show you how to make multiple selections in an Excel drop-down list (with repetition and without repetition).
This has been one of the most popular Excel tutorials on this site. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. So if you have any questions after reading this, please check out the FAQ section first.
There are two parts to creating a drop-down list that allows multiple selections:
- Creating the drop-down list.
- Adding the VBA code to the back-end.
Creating the Drop Down List in Excel
Here are the steps to create a drop-down list in Excel:
- Select the cell or range of cells where you want the drop-down list to appear (C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria.
- In Source field, select the cells which have the items that you want in the drop down.
- Click OK.
Now, cell C2 has a drop-down list which shows the items names in A2:A6.
As of now, we have a drop-down list where you can select one item at a time (as shown below).
To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end.
The next two sections of this tutorial will give you the VBA code to allow multiple selections in the drop-down list (with and without repetition).
VBA Code to allow Multiple Selections in a Drop-down List (with repetition)
Below is the Excel VBA code that will enable us to select more than one item from the drop-down list (allowing repetitions in selection):
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$C$2" 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 you need to place this code in a module in VB Editor (as shown below in the ‘Where to put the VBA code’ section’).
When you have placed this code in the backend (covered later in this tutorial), it will allow you make multiple selections in the drop down (as shown below).
Note that if you select an item more than once, it will be entered again (repetition is allowed).
Try it Yourself.. Download the Example File
VBA Code to allow Multiple Selections in a Drop-down List (without repetition)
A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.
Here is the code that will make sure an item can only be selected once so that there are no repetitions:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' 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 Target.Address = "$C$2" 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
Now you need to place this code in a module in VB Editor (as shown in the next section of this tutorial).
This code will allow you to select multiple items from the drop-down list. However, you will only be able to select an item only once. If you try and select it again, nothing would happen (as shown below).
Try it Yourself.. Download the Example File
Where to Put the VBA Code
Before you start using this code in excel, you need to put it in the back-end, such that it gets fired whenever there is any change in the drop-down selection.
Follow the below steps to put the VBA code in the backend of Excel:
- Go to the Developer Tab and click on Visual Basic (you can also use the keyboard shortcut – Alt + F11). This will open the Visual Basic Editor.
- There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).
- Double click on Worksheet Name (in the left pane) where the drop-down list resides. This opens the code window for that worksheet.
- In the code window, copy and paste the above code.
- Close the VB Editor.
Now when you go back to the drop-down and make selections, it will allow you to make multiple selections (as shown below):
Try it Yourself.. Download the Example File
Note: Since we are using a VBA code to get this done, you need to save the workbook with a .xls or .xlsm extension.
Frequently Asked Questions (FAQs)
I have created this section to answer some of the most asked questions about this tutorial and the VBA code. If you have any questions, I request you to go through this list of queries first.
Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells? Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then
Q: I need to create multiple drop-downs in entire column 'C'. How do I get this for all the cells in the columns with multi-select functionality? Ans: To enable multiple selections in drop-downs in an entire column, replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Column = 3 Then On similar lines, if you want this functionality in column C and D, use the below line: If Target.Column = 3 or Target.Column = 4 Then
Q: I need to create multiple drop-downs in a row. How can I do this? Ans: If you need to create drop-down lists with multiple selections in a row (let's say the second row), you need to replace the below line of code: If Target.Address = "$C$2" Then with this line: If Target.Row = 2 Then Similarly, if you want this to work for multiple rows (let's say second and third row), use the below line of code instead: If Target.Row = 2 or Target.Row = 3 Then
Q: As of now, the multiple selections are separated by a comma. How can I change this to separate these with space (or any other separator). Ans: To separate these with a separator other than a comma, you need to replace the following line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of VBA code: Target.Value = Oldvalue & " " & Newvalue Similarly, if you want to change comma with other character, such as |, you can use the following line of code: Target.Value = Oldvalue & "| " & Newvalue
Q: Can I get each selection in a separate line in the same cell? Ans: Yes you can. To get this, you need to replace the below line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of code: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine inserts a new line in the same cell. So whenever you make a selection from the drop-down, it will be inserted in a new line.
Q: Can I make the multiple selection functionality work in a protected sheet? Ans: Yes you can. To get this done, you need to do two things: Add the following line in the code (right after the DIM statement): Me.Protect UserInterfaceOnly:=True Second, you need to make sure the cells - that have the drop-down with multiple selection functionality - are not locked when you protect the entire sheet. Here is a tutorial on how to do this: Lock Cells in Excel
You May Also Like the Following Excel Tutorials:
Hi Sumit . Thanks a lot for this great video . In drop down tap beside the list there will be a option that will allow me to insert whatever i want to insert .Is it possible ? Please help me
Go to the Data tab and click on Data Validaiton. In the data validation dialog box, in the Error Alert tab, change the from Stop to Information. Now you will be able to make the changes and enter manually in the cell.
This has been really helpful, thank you so much. Is it possible to make this VBA code work when a sheet is protected?
You need to keep the cells with drop down unlocked. It will work fine then. Here is a tutorial on how to lock all the cells except some selected ones: https://trumpexcel.com/lock-cells-in-excel/
For Protected Worksheet:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Variant
For Each wsh In Worksheets(Array(“Sheet1″))
wsh.EnableOutlining = True
wsh.Protect UserInterfaceOnly:=True, Password:=””, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingCells:=False, _
AllowFormattingColumns:=False, _
AllowFormattingRows:=False, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=False, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=False, _
AllowSorting:=False, _
AllowFiltering:=False, _
AllowUsingPivotTables:=False
Next wsh
‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = “$C$2” Then ‘As required
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
Exitsub:
Application.EnableEvents = True
End Sub
This is a great bit of code and it’s working well for me. I have a question about whether or not I can use the code if the table I’m working in has been formatted as a table. Instead of “3” in the following line:
If Target.column = 3 Then
is it possible to use a reference to the table and column? Table1[column1]?
Hello, I followed the instructions and it worked perfectly. Is it possible to edit the above code so that when you click multiple selections it forms a list down multiple cells in a column, rather than in one cell separated by commas?
I have followed this to the letter and still only get one entry at a time. I have set up drop downs in every cell under column’s B,E,F. I want B to remain a single selection, but I want to have the option for E and F to have multiple entries (items) Any help would be appreciated
Thank you for your great work! If I need to select some Items and then add those in the cell, how can I make this?
hi did you ever figure this out??
Hello, How to I create multiple selections as a list in a new column rather than getting values separated by coma in same cell?
Hi Sumit, thanks for the codes. The multi-select list worked great but I have issue with the existing single-select list. Before I added your code, validation for the single-select list worked. I only could select the value from the list. But after I added your codes, the validation for single-select list didn’t work. I was able to enter any values to the single-select list and I didn’t get an error message. If I only select the value from the list then it’s fine. But I still could enter any value and the cell will accept it.
Do you know how to fix this? Thanks so much for your help!
Hi Sumit,
If I have to delete an entry from the list, it does not behave the way it should. Have you tried that?
awesome post, got it workin in under 5 minutes. Exactly what I wanted, thanks, keep up the good work
LIFE SAVER!!!! So here’s the tricky part. How do I now filter my column with multiple items so that everything with “A” appears…even though it has other names along with it “A,B”, “B,A”, “C,A”
I would like to know a solution for this also.
Great macro thanks! TRUMP RULES!
Hi Sumit, is there a modification to the code if I want the next selection from the dropdown in the next line. Like when we the ALT+Enter function : for eg
one
two
three
Instead of :
one, two, three
What is the solution to pepperleafev’s problem of duplicating values? I am facing the same problem after using the code
I am having the same issue…any answers???
Hello Sumit, thank you for supplying such helpful information, I have used the code and works well, not sure if this has been covered yet, but is there a way for the selected data to display down the column instead of in the one cell?
I came across your code chunk, and for the most part it works well.
A minor issue found:
FROM:
If Oldvalue = “” Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & “, ” & Newvalue
End If
TO:
If Oldvalue = “” Then
Target.Value = Newvalue
ElseIf Target.Value = Oldvalue Then ‘= 2 And Target.Row <
Thanks for sharing! Makes sense
I think I’m having the problem of duplicating values. If I make multiple selections from a drop down list for a cell and then try to put in something that is not from the drop down list, I end up with several repetitions of what I had selected in the same cell. Then when I try to delete some of the repeated values, it will repeat itself again in the same cell. So it becomes an endless cycle of repetitions until I just delete the entire cell. But I can’t figure out how to fix this. I tried to put in the code that spyrule shared but I may not be doing it right. I’m new to Vbasic so I don’t really understand the code…
This is what I ended up with:
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
If Target.Value = Oldvalue Then
GoTo Exitsub
Else
Target.Value = Oldvalue & “;” & Newvalue
End If
End If
End If
Application.EnableEvents = True
Thank you for posting. This was really helpful.
Hi Sumit, id you give an solution to this problem. I cannot seem to find a solution here.
Hi thank you for this nice tutorial. Now, instead of separating the multiple selections by a comma, I want to add the additional selections in the adjacent cells in the same row. But still have the feature of removing previously selected items. Can you please help me with that?
How does the code need to be changed if, instead of separating the multiple selection by a comma, the additional selections are added in separate cells in the same row? But still with the replacing feature included?
thank you very much for your help!!
Hi, I’ve been looking for this. This is a very great tip. Thank you so much for this advise.
Wish you the best,
Dara from Cambodia
The code worked great and I was so happy with finally being able to add my products within the same cell.
The next day however I went to open the file and now the code isn’t working??? It just went back to normal> I saved it as a Macro as well.
Does anyone know what I can do to fix this issue???
Please help and Thanks in advance!
I have had the same issue – even though the spreadsheet was saved as macro-enabled, the code never works when I close and reopen the spreadsheet
What if I wish to have more than one dropdown with different multible choice
Hi Sumit
a great thanks for great efforts
I have zero VBA knowledge, so used your code to work with
I already saved as XLSM, however
every time I enter a value in droplist, then try to select another value from it, I get an error “syntax error”
something strange, though I ready your code worked smoothly with other readers, only sadly with me, didn’t
I hope you can reply to me with solution or cause of error at least
thanks
webo https://uploads.disquscdn.com/images/6b616b8c296f18f73245dd1ceba825bad42f3a7e5982c0ec1d7b9ddfd4275874.jpg
Hi Sumit. I have read through all of the posts and it has helped me a lot. Just one more question if you don’t mind. I need a secondary list to select the items from that would only display the items that I selected in the first list. Please help me out as I am working for a company and this database needs this function immediately for me to start entering the data in it accordingly.
Thank You for your post and your help.
hello Kevin. Have a look at this tutorial: http://trumpexcel.com/2013/07/dependent-drop-down-list-in-excel/
Hi Sumit,
Thank you for sharing your code. I am able to get it to work except that when I make a 2nd selection (or 3rd, 4th, etc.), I get a green triangle trace error in the cell. It is saying that the value doesn’t match the data validation restrictions defined for the cell. Do you know what may be causing this and how to fix it?
Thanks
I’ve been playing around with this some more and have realized that the error only appears when I have my spreadsheet formatted as a table. I’m not sure why, but when it is not formatted, there is no error. Is there any way to fix this or change the code to address this?
Hey.. I tried converting the data into a table and see if I could replicate the error. It worked fine for me. Would be great if you could share your file. Can have a look and see what’s causing that.
Thanks, Sumit. I tried creating this in new worksheets as well, having the same problem. I would create the drop down list and it would work fine. But as soon as I “format as table” the trace error appears.
How can I send you the file?
Hi Joe & Sumit, were you able to finally get rid of the yellow triangle with an exclamation on the top left corner of the cell? I am assuming that was what you were also getting. Please let me know. Thanks.
Nevermind. I got it sorted by converting it from Table to Convert to Range. Thanks again for sharing.
Hi Sumit, Thanks for the code, but after I close it, I cannot run it. Usually I assign the maro to a shape box, but since it is a drop down menu, I couldnt assign a macro name and it is not running. Wondering why? Thanks!
Hi there,
I’m struggling a bit with what looks like others have been able to solve below. I have a spreadsheet where I’d like to enable multiple pick lists in columns E, M, and N only (down to row 100 or so in each). I don’t want multiple pick list in the other columns. Can you tell me exactly what to enter for the code? I’d be most grateful for your guidance.
Hi, when i attempt to filter a column that has cells with more than one value- the filtering system cannot pick out individual values, and instead picks them all per cell. Is there anyway to filter based on ONE value for all the cells(those that have many values and those that have one-separated by a comma?
Thanks. This was very useful. Precisely what I needed.
Hi, Can somebody help me change target is one address to one Range ==>
If Target.Address = “$C$2” Then
Thanks
This is great! Thank you so much for this. I have one question though. I’ve read the comments and can’t find the same question being asked – apologize if I missed it.
I am able to select multiple options from a dropdown box successfully, however I can’t find a way to them remove one of the options unless I clear the entire cell.
For example, I select options such that my cell looks like: Apple, Orange, Banana.
I no longer want Orange selected. If i try to delete the Orange text, it doesn’t work.
The only way I’ve found to do this is to delete all contents of the cell, then go back and select Apple and Banana from the dropdown.
Hello Shelley.. This is the drawback of using a drop-down list here. As soon as you delete an item and hit enter, Excel takes it as another entry that you are trying to make, and shows an error since that’s not a part of the drop down.
Hello Shelley.. This is the drawback of using a drop-down list here. As soon as you delete an item and hit enter, Excel takes it as another entry that you are trying to make, and shows an error since that’s not a part of the drop down.
Hi, this code seems to be working well for me, but can you tell me if it’s possible to ensure that once the selections are made they appear in alphabetical order?
Hi, am also facing this problem can you resolve this one?…
Me too. I need any suggestions for this case.
Hello I add the code to be used in C8 for 2 spreadsheets, the drop meny works only in C8 but I want it to be working until C200, please advise.
Angie
Hello Angie.. you can replace this line: If Target.Address = “$C$2” Then
with this line:
If Target.column = 3 Then
Now the drop down will work for all the cells in Column C
Hi there – I tried to use this code but it is not allowing me to add multiple values in 3 of my columns.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from http://www.trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = “$J$2” Or Target.Address = “$K$2” Or Target.Address = “$L$2” 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
Actually – it is working in all columns but only in cell 2 on each.
Same issue???
Yes same issue – actually I abandoned this option because I wanted to be able to filter on just one value within the whole field (set of suburbs for the South in one and wanted to filter on just one of those suburbs) but it won’t allow me to filter that way.
hello Ange
can you tell me what option you decided to use. thanks
Hi Sumit,
This worked great thanks, this question leads on from what I have now achieved with this code. Now that I have selected multiple entries in some cell (I have applied this code to an entire column) but not all, I would like to filter down to entries within that column, ie find all entries that contain orange or blue. So I have applied the usual filter to my heading row but when I click on this I would like the options to filter to appear the same way it would if there were only one entry in each cell, but it has the lists/multiple entries as options. Basically I want filter function to comma separate my lists I guess? Does that make sense? Any suggestions? I can just type in the colour in the search option under filter but that doesn’t work if I want multiple colors at once.
Thanks,
did you find a solution? i have been looking for days and there appears to be no way…
I am having an issue with the code. My lists are in sheet 2, but my drop downs are in sheet 1. How do I need to change the code to accommodate this? Thank you!
Great solution. How can I get each entry to go into an new line (issuing a line feed after the selection?
another thing how can I defind that with in a table in excel and that it could move automaticly with the table?
thanx
hi, followed this thread found it the most helpfull, but I need to tweek it a little bit more.
I need the selection to be words (strings) and after the selection the return value needs to be a sum of numbers, each word get its value – a number.
how can I do this ?
please help, I have been struggling with this one for three moths now.
thanx.
Thank you, this has been very helpful. I have set up multiple selection list in L5 using your code. In M5, I have a dependent selection list that recognizes a selection in L5. However, I am having trouble with this dependent list recognizing multiple selections. Any thoughts?
This is a great solution for MS Excel, do you have any idea if something similar would work for MS Project?
Hello Mark.. I am not sure if this can be done with MS Project. I know a guy who is an MS Project champion. Will ask him and post back
How do I make the macro work on a range of cells? For example cells L2 through L10000.
You’ll need to modify the code. Change the following line:
If Target.Address = “$C$2” OR Target.Address = “$D$2” Then
to
If Target.Column = 11 AND Target.Row > 1 AND Target.Row < 10001 Then
I was using
If Target.Address = “$G$2” Then
This worked on the one cell – allowing multiple selections in the same cell with a comma between.
I need to allow this on the entire column. When I change the code to
If Target.Column = 6 Then
Or
If Target.Column = 6 AND Target.Row > 1 AND Target.Row < 10001 Then I can not longer select multiple selections in the same cell. What am I doing wrong?
I need to know how to do TWO drop down lists with multiple choice selections on both . this must happen on the same sheet. Thanks
Hello Khushal.. You’ll need to modify the code. Change the following line:
If Target.Address = “$C$2” OR Target.Address = “$D$2” Then
Change the references to what you want.
Hi. Would this allow me to do two concurrent multiple choice selectons. I am assuming I need to have 2 target address in the VBA code.Forgive my zero sense of VBA. Thanks
Yes this should do it. The line in my last comment specifies two target address which would enable both the drop downs in the these cells to have multiple selection functionality
Hi. Just tried your suggestion and it works . Your’e great . Thanks
Hi Sumeet. I have tried your code suggestions and it works I have even tried 4 drop down list multiple selections and it works.
Just one question, when I save my work I am askes to save as a macro sheet. Is this the only way to save. What is your sugestio on the safest way to save. I may want to share this with others as well and they must be able to open the file.
You’ll have to save it in either .XLS or .XLSM format. Since it contains a macro, you can’t save it in the .XLSX format. Once you save it, it won’t show the prompt again. You can also share it with other people and there shouldn’t be any issue.
Help…saved as .XLSX format but when I save, close and reopen the coding is gone!
Yes, I have the same issue. I tried .XLS and .XLSM. Both happens the same… the code is gone after I reopen my file. 🙁
Hi Sumitji, I am wondering if there is a solution to this question. I “save as” and on the copy, the code is gone and I can no longer make multiple selections from the drop down list. I am creating a mental health treatment plan template so I would like to be able to use this template over and over again for new patients. Any ideas on how I can “save as” and retain the code and formatting?
Hi Sumit. Is it possible to have another drop down list under a different column? If yes, how do I do it? Thank you!
Hello Kirsten.. You can have the same functionality for any cell/column. You would need to change this line in the code:
If Target.Address = “$C$2” Then
If you want it for an entire column (say column D), make it:
If Target.Column = 4 Then
This is really great, I’ve been looking for this option. My question is, I’ve followed your instructions from above to modify the code and get it working on just one column, in this case column 7.
However, I’d like to get it working on column 6 too but instead of having the comma seperate each value I want to use a hyphen instead. So currently on column 7 the output is “1, 2, 3, 4”. On column 6 I want the output to be “1-2-3-4”.
I’ve played around with the code a bit but I can’t seem to get it right…any suggestions?
Hello.. Kindly have a look at this file: https://www.dropbox.com/s/7qbmv6k5ki3w0l9/Multiple-Selection-from-a-Drop-Down-List-in-Excel_Custom%20Separator.xlsm?dl=0
Can you use this concept and remove options as they are chosen. The additional tutorial above Creating Multiple Drop-down Lists in Excel without Repetition removes but its multiple cells. I want something that allows multiple selections as above tutorial but removes them as you choose them so they are not duplicated. TIA
Hello Emily, you can use the below code to make sure an option doesn’t get selected multiple times:
Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$C$2” 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
Is this code in addition to the original code in the tutorial? You didn’t specify if this is a replacement or in addition to. TIA. Great tutorial by the way.
Hi i am also facing the same issue how can we run the code while having the sheet protected… pls help its really urgent
Hi how can we run the code while having the sheet protected… pls help its really urgent
Great post. Followed it but can you suggest what to do if we want to delete / remove a selection made earlier. I mean suppose we select three mutiple options and i have to remove second one then how to do it? Also how to apply this to entire column (i.e from c2 till end)
You can modify the code to automatically delete an entry when you select it again, but I believe it would be easier if you simply delete it manually (unless you have tens/hundreds of options selected. To apply this to all the cells in column C, replace the line
If Target.Address = “$C$2” Then
with
If Target.Column = 3 Then
Hi Sumit, I have run into a snag. I started with your code at the top of the thread and started making changes based on your recommendations.
1. Allow for multiple selections. (First code given on page) -Works.
2. changed the code from Target.address = “$C$2 Then to If Target.Column = 10 And Target.Row > 3 And Target.Row < 43 – Works.
3. I changed the code per your recommendation so you can't select the same option again. – Works.
I ran into a snag when I need to un-select a previously selected item from the list. I tried to delete the text in the cell, but it gives me the error box. (Use case is that after review with teams, we need to change the selected teams)
Second Question, From a user experience perspective do you have a way to do this with Checkboxes so you can select all at once (either selecting or deselecting) the radio buttons for each item?
Thanks for all your help!
Doug
Hi Sumit!
Thanks in advance for providing us the code for multiple selection in drop down list. I am facing the problem in deleting. As soon as I delete any wrong selection from the list and hit the enter or tab key, it re-appears on the same list. Please help
Hi,
Were you able to find any solution for deleting/removing previously selected items?
https://www.youtube.com/watch?v=cRpTzOnaf48
This might help.
It was previously posted by Epps
I usually just right click on the cell and choose “clear contents” and then I can start over.
Hi Sumeet,
Thanks for the code! Can you please let me know how can i deselect an entry?I am not able to delete an entry manually.
Thanks in advance.
Regards,
Soumya
this is really cool! thanks a lot! Can I have combine with the drop down ists in Excel without Repetition?
Hello Anu.. Below is the code you can use. If there is already a value in the cell and you select it again, it will not append it to the existing value.
Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from http://www.trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$C$2” 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
I am working with 2010 Excel. I am right clicking on the sheet name to bring me to the “View Code” option. However, once I insert your code into the box, nothing is happening. What could I be doing incorrectly? Aside from trying code when I have no business doing that…=)
Hello Arielle.. I have hard coded the cell C2 in the code. You will have to change that cell reference for it to work for you. For example, if you drop down is in cell D2, then change it to D2. Or if there are multiple drop downs in column D, then use Target.Column = 4
hi sumit, how do I get the target.culumn = 4 to work for two different columns in the same sheet
hi sumit, how do I get the target.culumn = 4 to work for two different columns in the same sheet
Hello Edwin. You can use OR in the code to apply it to multiple columns. For example, you can use Target.Column = 4 or Target.Column = 5
The code to allow multiple selections works great. Do you know how to allow editing of the cells after selections are made? I can’t seem to remove previously selected items. Thanks
https://www.youtube.com/watch?v=cRpTzOnaf48
Here is VBA code included with video for drop down list with multiple selection that also allows you to remove previously selected items by reselecting them.
Could you share the code as this is just what I am looking for? Thanks
Hi Sumit,
Whenever I close the excel file and re-open the code disappears. I have to re-paste the code every time. Any solution?
you have to enable macros when reopening. Is it saved as macro-enabeled workbook?
Hi, followed this successfully, thanks for the tips. That said, I’m unable to replicate using this code, even when using “Or” commands along the “$C$2” line, to have the code apply to more than one drop-down list within the main workbook. Can you advise me on how I can write/adjust the code such that I can have multiple drop down lists where I am able to select more than one option?
For clarification, I want to build a matrix/table where I can select multiple drop-down options across 3-4 columns and 25-40 rows. So, wondering how I will need to adjust the “$C$2” part of that code to include the code for all of the cells in which I’d want to do a multi-select. That make sense?
Hello Jason, If you want this to be applied to all the drop downs in your worksheet, remove the following line from the code:
If Target.Address = “$C$2” Then
Also remove one the END IF from the end of the code.
Hello. This information was vital, thanks.
But if I don’t want to apply the code to all the drop downs in the worksheet, only in on row? For example, only in g5:g53?
Can you tell me how to do this?
Hello Claudia.. In the code, you can replace the line
If Target.Address = “$C$2” Then
with
If Target.Column = 7 And Target.Row > 4 And Target.Row < 54 Then
Hello Sumit; thank you so much, it was just what I needed.
I want to apply the VBA Code to cells C7:C80. Is the following correct “If Target.Column = 3 And Target.Row > 6 And Target.Row < 81 Then"? Thanks!
Phil thank you so much this was just what I needed
Thank you Sumit! It worked for me today. Happy New Year 2018!
When I did this it worked for the drop downs but it also caused every cell to show multiple entries. Is there a way to apply it to a specific number of cells, say 5. Thanks in advance.
Hello Emily.. You can specify the cells in this line
If Target.Address = “$C$2” Then
For example, if you want the drop down to work on C2 and C3, use:
If Target.Address = “$C$2” OR Target.Address = “$C$3” Then
Thanks so much for the assistance. Everything is working well now. I do have anther question. Is it possible to have an option for the user to add their own entry to a list? I know I can turn off the the error message and allow them to type something, but I want their to be an item on the list like “other” and then when they select it they can enter their info. How can I make this work? Any suggestions. I have spent an hour searching online without any results. TIA
Hi Sumit – Thank you for this wealth of knowledge! I am trying to apply this code to 3 different drop down lists in the same worksheet – cells P7:P70; AD7:AD70 and AH7:AH70. How can I do that? Thank you!
Never mind…figured it out! Thanks for the post/information though!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngDV As Range
Dim Oldvalue As String
Dim Newvalue As String
If Target.Count > 1 Then GoTo Exitsub
On Error Resume Next
Set RngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo Exitsub
If RngDV Is Nothing Then GoTo Exitsub
If Intersect(Target, RngDV) Is Nothing Then
‘do nothing
Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
Target.Value = Newvalue
If Target.Column = 16 Then
If Oldvalue = “” Then
‘do nothing
Else
If Newvalue = “” Then
‘do nothing
Else
Target.Value = Oldvalue _
& “;” & Newvalue
End If
End If
End If
If Target.Column = 30 Then
If Oldvalue = “” Then
‘do nothing
Else
If Newvalue = “” Then
‘do nothing
Else
Target.Value = Oldvalue _
& “;” & Newvalue
End If
End If
End If
If Target.Column = 34 Then
If Oldvalue = “” Then
‘do nothing
Else
If Newvalue = “” Then
‘do nothing
Else
Target.Value = Oldvalue _
& “;” & Newvalue
End If
End If
End If
End If
Exitsub:
Application.EnableEvents = True
End Sub
Thanks, I needed this
I want to apply your code (allowing one instance of multiple choices from a drop down) but it applies this code to the whole sheet so cells that are just to be typed into can have multiple entries, so if someone types into a cell then goes back and wants to over write this info it appears side by side in the one cell with a , seperating them,
I only want to apply your code to cells C12 to C16 and have made the follwoing adjustments:
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 = “C12” Or Target.Address = “C13” Or Target.Address = “C14” Or Target.Address = “C15” Or Target.Address = “C16” 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
‘ I just swapped out Target.Address for Target.Column. Tested and working.
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.Column = “C12” Or Target.Column = “C13” Or Target.Column = “C14” Or Target.Column = “C15” Or Target.Column = “C16” 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
Hi Sumit, This is really helpful but I’m not sure which “END IF” to remove. There’s three in the code, do I remove all three?
Sumit – I was wonder if you could assist me. I have a column titled ‘Services’ and have created dropdown list in each cell. The worksheet has about 186 rows (and growing). I used this code to be able to select multiple and have them show up in each cell. I took out ‘IF Target.Address = “$C$2” Then and one END IF and not it works for all cells that has the dropdown list. HOWEVER, whenever I type anything in any other cell, it doubles/replicates what I already had in there plus what I was typing. I’m guessing that is because of the code I put in for the dropdowns. Could you help?
thank a lot. your a life safer 🙂