You can use VBA to select multiple items from an Excel drop-down list and keep them together in the same cell.
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:
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.
Follow along with the example file
Multiple Selections in Excel Drop Down List.xlsm
Watch Video – How to Select Multiple Items from an Excel Drop Down List
The video shows the original approach. Use the code below for the examples here, including the corrected check that treats New York and York as different cities.
Create the Drop-Down List
There are two parts to creating a drop-down list that allows multiple selections: creating the list and adding the VBA code to the worksheet.
Excel’s standard Data Validation list doesn’t have a setting that keeps successive choices in one cell. The VBA below adds that behavior; a formula or Office Script isn’t a direct replacement for this selection-change event.
These examples were tested in desktop Excel on Windows. Excel for the web cannot run VBA, and this workbook has not been tested on Mac.
Below I have a list of cities, and I want to select several of them in cell C2.
The example file already has the lists and code. If you’re building this in your own workbook, enter the five cities in A2:A6, then follow these steps:
- Select cell C2, or select the range of cells where you want the drop-down lists.
- Click the Data tab.
- Click Data Validation in the Data Tools group.
- On the Settings tab, choose List from the Allow menu.
- In the Source field, enter
=$A$2:$A$6.
- Click OK.
Now, cell C2 has a drop-down list showing the cities in A2:A6. At this point, choosing another city still replaces the previous one.
Select Multiple Items Without Duplicates
A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.
Below is the city list from the No Duplicates sheet, where the selections will go into C2.
Here is the VBA code. Copy the entire block, including the helper function at the end:
Option Explicit'Paste into the worksheet's code module, not a standard module.'Use the drop-down or type one valid choice. Bulk edits replace values.'Ordinary paste can replace validation. Use Paste Values for a single choice.'Other VBA that writes to these inputs must disable events around its writes.Private Sub Worksheet_Change(ByVal Target As Range) Const InputCells As String = "C2" Const Separator As String = ", " Const AllowRepeats As Boolean = False Dim NewValue As String, OldValue As String, Result As String Dim Entry As Variant, AlreadySelected As Boolean Dim PreviousEvents As Boolean, EventsChanged As Boolean Dim Undone As Boolean, Failure As String Dim ValidChoice As Boolean If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range(InputCells)) Is Nothing Then Exit Sub If Not HasListValidation(Target) Then Exit Sub On Error GoTo Failed If Not IsError(Target.Value2) Then NewValue = CStr(Target.Value2) If Len(NewValue) = 0 And Not Target.HasFormula And Not IsError(Target.Value2) Then Exit Sub ValidChoice = Target.Validation.Value And Not Target.HasFormula And Not IsError(Target.Value2) PreviousEvents = Application.EnableEvents Application.EnableEvents = False EventsChanged = True Application.Undo Undone = True If IsError(Target.Value2) Or Target.HasFormula Then OldValue = "" Else OldValue = CStr(Target.Value2) End If If Not ValidChoice Then Failure = "Choose one city from the drop-down list. The previous selections have been kept." GoTo Finished End If 'The separator cannot also appear inside an individual list item. If InStr(1, NewValue, Separator, vbBinaryCompare) > 0 Then Failure = "This item contains the selection separator. Choose a separator that is absent from the list items." GoTo Finished End If If Len(OldValue) = 0 Then Result = NewValue Else If Not AllowRepeats Then For Each Entry In Split(OldValue, Separator) If StrComp(CStr(Entry), NewValue, vbTextCompare) = 0 Then AlreadySelected = True Exit For End If Next Entry End If If AlreadySelected Then Result = OldValue Else Result = OldValue & Separator & NewValue End If End If If Len(Result) > 32767 Then Failure = "The selections would exceed Excel's cell text limit. Clear this cell and start a shorter list." GoTo Finished End If Target.Value2 = ResultFinished: If EventsChanged Then Application.EnableEvents = PreviousEvents If Len(Failure) > 0 Then MsgBox Failure, vbExclamation, "Multiple selections" Exit SubFailed: Failure = "The selections could not be combined: " & Err.Description 'Undo may be unavailable for a programmatic edit. Do not undo again. On Error Resume Next If Undone Then Target.Value2 = OldValue If EventsChanged Then Application.EnableEvents = PreviousEvents On Error GoTo 0 MsgBox Failure, vbExclamation, "Multiple selections"End SubPrivate Function HasListValidation(ByVal Cell As Range) As Boolean On Error Resume Next HasListValidation = (Cell.Validation.Type = xlValidateList) On Error GoTo 0End FunctionVBA example by Sumit Bansal, https://trumpexcel.com.
This is a worksheet event: Excel runs it when you change the selection cell. Put it in that worksheet’s code window, not a standard module, and don’t press F5 to run it.
Here are the steps to add it to your workbook:
- Press Alt + F11 to open the Visual Basic Editor.
You can also open the editor from the Developer Tab by clicking Visual Basic. If the Project Explorer pane is missing, press Ctrl + R to show it.
- In Project Explorer, expand your workbook’s Microsoft Excel Objects folder.
- Double-click the worksheet that contains the drop-down list.
- Paste the complete code into that worksheet’s code window.
If that sheet already has a Worksheet_Change procedure, don’t paste a second one below it. The two pieces of event code need to be combined into one procedure.
- Close the Visual Basic Editor to return to Excel.
- Save the workbook as Excel Macro-Enabled Workbook (*.xlsm).
The older .xls format can also store VBA, but use .xlsm for this workbook. Saving as .xlsx removes the VBA needed for multiple selections.
When reopening the workbook, enable macros only if you trust the file. If your organization blocks macros, check with your IT team; you don’t need to enable Trust access to the VBA project object model to run this code.
To try the downloaded No Duplicates sheet, clear C2 first because it contains sample selections. Then choose New York followed by York from the drop-down.
You get New York, York in C2. If you select York again, the value stays the same, because the code compares complete city names rather than looking for one name inside another.
The comparison ignores capitalization, so Boston and BOSTON count as the same choice. Selecting an existing city does not remove it; press Delete to clear the cell and choose the cities again.
The code briefly undoes the new selection to read what was in the cell before, then combines the values. It turns events off while writing the result so that its own change doesn’t trigger the same code again.
Important: This macro uses Excel’s Undo history internally, so you cannot rely on Ctrl + Z to undo its changes. Keep a copy of the workbook before making substantial changes.
Use the drop-down to add one city at a time. Typing one valid city also works, but replacing the cell with a whole comma-separated list isn’t the same as making individual selections.
Ordinary paste can replace the cell’s Data Validation rule and stop the multiple-selection behavior. Use Paste Values for a single choice; the code rejects a value outside the list while keeping your previous selections.
Changes to several cells at once are left as ordinary edits, rather than being appended. If other VBA writes to these cells, that code must disable events around its writes because programmatic changes don’t create the same Undo history as your selections.
Allow Repeated Selections
The Allow Repeats sheet has the repetition version already set up in C2. Note that if you select an item more than once, it will be entered again (repetition is allowed).
Below is the same city list, with the repeated choices kept together in C2.
In the worksheet code, replace the AllowRepeats line with this one:
Const AllowRepeats As Boolean = TrueKeep the rest of the code, including the helper function, unchanged. Use this setting in the existing procedure rather than adding another copy of Worksheet_Change.
Clear C2 on the downloaded sheet before trying it, because the cell already contains sample selections. Then selecting Boston, Austin, and Boston again gives you Boston, Austin, Boston.
Follow along with the example file
Multiple Selections in Excel Drop Down List.xlsm
Use Multiple Selection in Other Cells
Since I get a lot of similar questions, I have created the following sections 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 these sections first.
The code starts with C2 as its target. On the Several Cells sheet, C2, C3, and C4 each keep their own selections from the city list.
To use those three cells, replace the InputCells line near the top of the code with:
Const InputCells As String = "C2:C4"Make sure each target cell also has a List validation rule. Changing this line tells the macro where to work; it doesn’t create the drop-down lists.
You can use the following range variations in your own workbook. Replace the same InputCells line with the one you need, rather than adding all of them.
For every list-validation cell in column C:
Const InputCells As String = "C:C"For columns C and D:
Const InputCells As String = "C:D"For row 2:
Const InputCells As String = "2:2"For rows 2 and 3:
Const InputCells As String = "2:3"Cells without List validation are ignored, even if they’re inside the specified range. Each worksheet that needs this behavior must have its own event code.
These examples accumulate choices within each cell. If you want a choice used in one cell to disappear from other cells’ lists, see Creating Multiple Drop-down Lists in Excel without Repetition.
Change the Separator or Put Each Selection on a New Line
The comma examples use a comma followed by a space between choices. The Separator setting controls both how values are joined and how the no-duplicates version identifies individual choices.
To use a vertical bar followed by a space, replace that setting with:
Const Separator As String = "| "You can use a space alone with a list of single-word choices:
Const Separator As String = " "A space alone won’t work with this example’s New York entry because that city already contains a space. Choose a separator that appears in none of your list items; the code rejects a choice containing the separator.
Clear any existing selections before changing the separator. Otherwise, the old text still uses the previous separator, so the code cannot reliably split it into individual choices.
For a new line in the same cell, use the Line Breaks sheet. Below is the city list with selections displayed on separate lines in C2.
In that sheet’s code, the separator is:
Const Separator As String = vbLfvbLf supplies the line-feed character Excel uses inside a cell. The original example used vbNewLine; this version uses vbLf to insert the Excel cell line break directly.
If you’re adding this version to your own sheet, make the line breaks visible:
- Select the cell containing the selections.
- On the Home tab, click Wrap Text.
- Increase the row height so that all the selected cities are visible.
The example file reserves enough height for all five cities. Selecting New York, York, and Boston puts each city on its own line instead of separating them with commas.
Use the Drop-Down on a Protected Sheet
You can keep the city source list protected while allowing selections in C2. The Protected Sheet example does this by leaving C2 unlocked before protecting the worksheet.
For your own worksheet, set up the drop-down and event code before applying protection. Then follow these steps:
- Select the cells that should remain editable, such as C2.
- Press Ctrl + 1 to open Format Cells.
- On the Protection tab, clear the Locked checkbox.
- Click OK.
- On the Review tab, click Protect Sheet.
- Choose your protection settings and click OK, confirming your password if you set one.
The macro writes only to the unlocked selection cell, so this version doesn’t need Me.Protect UserInterfaceOnly:=True. The protected example was tested again after closing and reopening the workbook.
The example sheet uses demo as its demonstration password. If you need to change its source list, unprotect the sheet first; for more detail on choosing editable cells, see Lock Cells in Excel.
Follow along with the example file
Multiple Selections in Excel Drop Down List.xlsm
You can now keep several choices in one cell, with or without repeated items. Save the workbook as .xlsm so the code stays with it when you reopen it.
Other Excel Articles You May Also Like:
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 🙂