Data validation lists will only let you choose one item from the list. But what if we have a situation where we want to choose more than one item?
Watch the Video
When a selection is made from the DV list, we can intercept the change using a Worksheet_Change event.
Worksheet_Change is triggered by a change to any cell on the sheet, so the code needs to check if the changed cell is using our data validation list.
As usual the changed range is passed into the event with a variable called Target, and I've created a named range, DV_Range, that refers to C1:C4 where I'm using the data validation.
If Target and DV_Range Intersect then the data validation lists are being used. If they don't intersect then the code doesn't need to do anything.
Here's the trick. Store the newly selected choice from the DV list. At this point the cell has been changed and shows only the new selection from the DV list.
Undo the change
The cell now contains the value before a new DV selection was made, store this value.
The code now needs to do one of three things:
- If the old value was nothing, the cell should contain the new selection
- If the new value is nothing, the cell contents are being deleted
- If neither of these things are true, join the old value and the new value (they are strings), separated by a comma in this case, and put this into the cell.
This can be done using these IF statements:
That's all there is to it.
Update : Remove Previously Selected Choice
With the original code if the same choice was made again, nothing happened. With this update, selecting the same choice again removes it from the cell.
Download the example workbook to use this code yourself.
Download the Workbook
Enter your email address below to download the sample workbook containing the code in this post.
Thank you so much for this code. I was wondering if something could be done to enter each selection on its own cell below?
The correct way to enter the data into separate cells is to put a regular data validation list in each cell.
Hi – thanks for this code.
I tried modify it to work on a protected sheet by adding:
ActiveSheet.Unprotect
before
Application.EnableEvents = False
…and then adding:
ActiveSheet.Protect
after
Application.EnableEvents = True
…but that didn’t work. The sheet remained protected the next time I tried to to select from the drop-down in another cell in DV_Range
Hi,
Adding Sheet.Protect will obviously protect the sheet.
If you want some cells to be accessible to user, you have to set the cell format to be unlocked, you can right click the cell>Format Cells>Protection Tab-uncheck the Locked checkbox.
Hi normally drop-down lists work in locked cells when the sheet is protected – i.e. you can make a selection, but not paste in new values. When I try this code on a protected sheet, it doesn’t allow the user to select from the drop-down list (let alone multiple selections. That’s why I thought perhaps it could be modified to first unprotect the sheet when the focus / selection is on the DV_range, so that the select/multi-select function works, then re-protect the sheet when the user focus / selection is outside the DV_range.
…but I don’t know how to do that. What I tried (above didn’t work).
Hi,
Normally drop-down lists DON’T work in locked cells when the sheet is protected, you can only press the button to show the list, but once you select something you’ll get an error.
You have to unprotect the sheet when you select a cell from DVRange, not when changing the cell, so use SelectionChange event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, ActiveSheet.Range(“DV_Range”)) Is Nothing Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If
End Sub
Thanks Catalin. That means unless that range is selected I can’t unprotect the sheet. How would it be modified to make sure the sheet is unprotected when the DV_Range range is selected but return the sheet to it’s protection status when the DV_Range range is not selected?
Thanks Catalin.
Eventually I went with the following to make sure the sheet is unprotected when the DV_Range range is selected but return the sheet to it’s protection status when the DV_Range range is not selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ Check if the target range intersects with the defined range
If Not Intersect(Target, ActiveSheet.Range(“DV_Range”)) Is Nothing Then
‘ If it does, unprotect the sheet if it’s protected
Dim isProtected As Boolean
isProtected = Me.ProtectContents
If isProtected Then
Me.Unprotect
End If
Else
‘ If it doesn’t, restore the original protection status
If isProtected Then
Me.Protect
End If
End If
End Sub
Great, glad to hear you managed to do what you wanted.
Of course, without a password protection, users can manually unprotect at any time. And more, if you set a password in code, you have to protect the vba password to keep the pass away from users.
Hi,
Great code, helped me a lot.
One thing I noticed in the update: Remove Previously Selected Choice. There is no need of the IF that prevents the same value appearing in the cell multiple times, since selecting the same choice again already removes it from the cell.
BR
Thank you for sharing Agnes, good point!
Hello,
I am having problems unzipping the file. Can someone help me?
Hi Kathy,
What zip file? There isn’t a zip file to download for this blog post???
Regards
Phil
Thanks, how can I do it in excel online?
No, macros cannot run in Excel Online.
Hi,
I would like to reset the cell. For example I choose data1, data2, data3 from the list then I want to delete them all at once instead of choicing them one after the other. how to do that? because when i used delete ( which works perfectly with drop down list single choice), VBA did not work and the list changed from multiple choice to single choice and i had to close excel and open it to work again.
Thanks
Hi Tammy,
Deleting the cell contents won’t stop the VBA from working. If the code in your workbook isn’t working after deleting the cell contents, something else is going on.
Perhaps you can post the file you’re having issue with on our forum for me to take a look at.
Regards
Phil
Thanks, should I post it in Public Forums in VBA and Macros?
Hi Tammy, yes that would be fine.
“Remove Previously Selected Choice”
This works for me on one column, thanks. How do I edit the code to apply it to multiple columns?
say col D-H
or
col D-H and L-T
Thanks
Hi Nod,
I’ve already done this for another person – see this comment and the file that can be downloaded there
https://www.myonlinetraininghub.com/select-multiple-items-from-drop-down-data-validation-list#comment-94283
Regards
Phil
The excel file didn’t have the VB code in it.
Hi Lucy,
It definitely does. It’s in the Sheet1 module.
regards
Phil
Hi,
can I have two differents code in the same sheet for two different selection zone (as the “DV_list” in your example). If so, how does it ork?
Hi Claude,
Yes you can do this. Create a new list to use as your 2nd data validation list. Then create a new name for that list e.g. DV_List2.
You also need to create a new name for the range that this new DV list be be used in e.g. DV_Range2.
You can then modify the VBA to take account of these new names by modifying this line to:
If Not Intersect(Target, ActiveSheet.Range("DV_Range, DV_Range2")) Is Nothing ThenDownload this file which includes working examples of everything I just described.
Regards
Phil
Your downloadable workbook does not work.
https://d13ot9o61jdzpp.cloudfront.net/files/Select-Multiple-Items-In-Drop-Down-Data-Validation-List-Remove-Previously-Selected.xlsm
Hi Stephen,
I test all of these workbooks before releasing them and it works ok for me.
How exactly does it not work for you?
Regards
Phil
Thanks for sharing your knowledge. Can you help me with searching for the value with the same functionality?!
Because my table list has 100+ names. It’s difficult to find and pick from dropdown and select multiple items.
Hi Veera,
There’s no in-built way to search a drop down list. There are solutions using VBA/forms and one with dynamic arrays. I’ll look to writing that up soon in a new blog post.
Regards
Phil
Thanks for the code. Unfortunately, I cannot make this work for me when I copy and paste the code to another worksheet.
It gives me an error with the line:
If Not Intersect(Target, ActiveSheet.Range(“Nameofrange_Range”)) Is Nothing Then
Yet, it will run the code when I rightclick and press RUN TO CURSOR.
I am trying to include multiple multiple-choice dropdown lists on one excel sheet.
Thank you.
Hi Aisha,
Without seeing your code it’s hard to be certain about the reason you are getting an error, but it may be because the active sheet e.g. ActiveSheet.Range(“Nameofrange_Range”). Try changing this to reference the sheet where the DV list is e.g. Sheets(“Sheet1”).Range(“DV_Range1”).
Or it may be that the name of your range is not defined.
If you can’t get it figured out, please start a topic on our forum and attach the workbook.
Regards
Phil
Hi, Has anyone got a script on this working within excel online (using office scripts)?
I don’t know if it can be done but I developed a spreadsheet for desktop use but now the client wants to only use online
Thanks
Hi JOhn,
Sorry nope, I haven’t tried using this in Excel Online.
Regards
Phil
Hi I have replicated your code to have multiple values and it is working great.
Can you advise if I want to do this on more than 1 set of data in the same workbook what section of code I need to reuse or how I can do this
The columns with data validation requiring 2 or more values are all on the one tab and I have a separate sheet for data validation
Hi Elaine,
The key is in the initial check:
If Not Intersect(Target, Range(“DV_Range”)) Is Nothing
This is the code that detects where your data validation is, you can add more ranges:
If Not Intersect(Target, Range(“DV_Range1, DV_Range2”)) Is Nothing
Hi there,
I’ve tried to add more than one multiple select ranges in the same workbook by changing the initial check to:
If Not Intersect(Target, Range(“DV_Range1, DV_Range2”)) Is Nothing
As provided above but I get an error.
Thanks
Lee
Hi Lee,
Can you provide a test file so we can see what happens?
Create a new topic on our forum, you will be able to attach a file there.
I’m referencing a dynamic range via an offset formula in my data validation. any way to get this code to play nice?
Hi Allen, That should work fine. Please post your question on our Excel forum where you can also upload a sample file and we can help you further. Mynda
Hi. I’ve just tried copying the multiple selection VBA code into my own spreadsheet – even used the same “DV_Range” naming however it doesn’t work. Have enabled macros – anything else I need to do?
Hi Peter,
Please upload your test file on our forum so we can see what’s wrong.
Hi, New to using excel and got to say this website has been a great way to learn 🙂
A quick question regarding this post: I’ve worked out how to add additional columns (with different validation lists) on the spreadsheet from an answer in the forum.
this > If Not Intersect(Target, ActiveSheet.Range(“DV_Range, NEW_Range, ETC_Range”)) Is Nothing Then
If I placed my lists (e.g. dv_list) onto a different sheet can I still get the code to work by changing ActiveSheet? Or are we not able to do this for some reason? If we can how would i change the code to link to my new sheet?
Thanks,
Tom
Hi Tom,
If the data validations lists are not in the current sheet, just use full reference to the lists sheet instead of active sheet:
If Not Intersect(Target, ThisWorkbook.Worksheets(“Lists”).Range(“DV_Range, NEW_Range, ETC_Range”)) Is Nothing Then
Wow! Thanks for the quick response – I really appreciate the help 🙂 Keep up the great work guys.
Hi, can you help me with a generic example code for the same case?, I’ve downloaded the VB that was showed above and I tried to modify it and extend it to additional columns (with different validation lists) but it didn´t worked.
Thanks
Fabián
Hi Fabian,
We need to see your code to figure out how to make it work.
Please start a topic on our forum and attach your file(s)
Regards
Phil
Hi. Great!
However I encountered one (minor) issue: when there are already 2 or more values in a cell in DV_Range and I manually enter a value that is not in dv_list, Excel shows the dialog box “This data doestn’t match the data validation restrictions defined for this cell.” – Retry / Cancel. Selecting “Cancel” triggers a Run-time error ‘1004’.
Hi Stefaan,
Sorry I can’t reproduce that error. If I have 2 entries in the cell and then type something else in, I get the This value doesn’t match ….. error, but clicking Cancel just removes what I typed leaving the cell unchanged.
What version of excel are you using?
Regards
Phil
Wow, this is great! Thanks,
is there a way to have new chosen values to appear in the next cell below? To make a list directly under the field of choice?
Hi Janusz,
Yes this could be done. Can you please start a topic on our forum and attach your file so I can work with it.
Regards
Phil
Works as advertised, but when applied to a column in a table I get a data validation error when more than 1 selection is made.
Thank you.
Hi Scott,
Please start a topic on the forum and attach your file. Can’t debug an issue without seeing the code and data.
regards
Phil
Thank you for this! I would like to use this in different columns (with different validation lists) in the same sheet. So what I did was just copy-past the code after the first one and changed the ActiveSheet.Range. But I am getting an error and this line gets highlighted:
Private Sub Worksheet_Change(ByVal Target As Range)
I’m not sure if I should change this since I’ll be working in different ranges in the same sheet? I’ve no idea about VBA code, to be honest. Could you please help? Thank you!
there can be only one Private Sub Worksheet_Change(ByVal Target As Range)
Add another Intersect condition for a different range, in the same Change procedure:
If Not Intersect(Target, NewRange) is Nothing then
End If
I don’t understand.
can you please just tell me where should I type which code to do this? if you can type it and tell me to copy paste that’d be great
please help. thanx
It will be easier to help you if you can upload a sample file with your code on our forum.
Here is a link, please create a new topic and upload your sample file.
Wow, this is great! Thanks for posting.
Is there a way to increase the number of entries that appear in the selection pop-up? It seems to only show eight options even though I have thirty options in the dv_list range. If it could show all of the options without scrolling, that would be ideal.
Also, is there a way to change the delimiter from a comma to char(10) ?
Hi Matthew,
Just replace:
Target.Value = OldVal & “, ” & NewVal
with:
Target.Value = OldVal & Chr(10) & NewVal
There are a few more places that works with that separator, make sure you replace the “, ” separator with yours ( Chr(10) ):
If InStr(OldVal, “, “) Then
If InStr(OldVal, “, ” & NewVal) Then
Target.Value = Replace(OldVal, “, ” & NewVal, “”)
Hi Matthew,
Not in the standard data validation menu, the only alternatives are :
-userform, or
-insert an ActiveX control – combobox, from Developer tab>Insert, you can set there the default number of entries displayed (ListRows property)
Of course, you will have to adjust the code to work with that object.
I am using the code below. Can you help me so that this applies to every cell in column D?
‘ To allow multiple selections in a Drop Down List in Excel (without repetition)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$D$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 & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Hi Jamaal,
The code I’ve written in this blog post already does this – did you try it?
To do what you want replace this line
If Target.Address = "$D$2" Thenwith this
If Target.Column = 4 ThenRegards
Phil
This code will not work for me in a file made with excel 2013, what am I doing wrong? was able to use it in files made with later versions of excel.
Hi Sarah,
It should work in Excel 2013. If you post your question and file on our forum we can help troubleshoot for you.
Mynda
I replicated the code and I can select multiple items from the drop-down list, thank you!
I have 2 followup questions:
1)If you select a list item in error and want to edit the cell to remove it, it gives a validation error. The only way to fix is erase the contents and start over. Any fix for this?
2)I have more than one column on spreadsheet that I would like a multi-select drop-down for. They are independent of each other. I replicated the code in another sub name with the different range name, but it won’t work. It rendered both subs inoperable.
Hi Lynn,
1) I’ve updated the code to remove a previously selected choice, you can download the workbook again to get this new code.
2) I’d need to see your code and workbook to figure out what is wrong. Please start a topic on the forum and attach your workbook.
Thanks
Phil
I’ve used your newest code (just downloaded it) but it doesn’t remove the same value when chosen again in the drop down menu. Can you give it another look?
Nevermind, it seems to work in your excel sheet – however I’m not sure which part does the magic. Could you tell me so I can understand where it’s happening?
Thanks a lot!
Hi Thomas,
I’ve written comments in my code to explain what it is doing
‘ If selection is already in the cell we want to remove it
If InStr(OldVal, NewVal) Then
‘If there’s a comma in the cell, there’s more than one word in the cell
If InStr(OldVal, “,”) Then
If InStr(OldVal, “, ” & NewVal) Then
Target.Value = Replace(OldVal, “, ” & NewVal, “”)
Else
Target.Value = Replace(OldVal, NewVal & “, “, “”)
End If
Else
‘ If we get to here the selection was the only thing in the cell
Target.Value = “”
End If
Else
If the cell contains Alpha and you select Alpha again, then the cell contents will become empty
If the cell contains (for example) Alpha, Bravo and you select Alpha, then remove “Alpha, ”
If the cell contains (for example) Alpha, Bravo and you select Bravo, then remove “, Bravo”
You will need to understand how the Instr and Replace functions work
VBA String Functions
Regards
Phil
This is great, thank you so much!
You’re welcome.
Hi!
How can I get the values sorted in the same order as they appear in the drop down list?
If I select “Bravo, Charlie, Delta” and then on the next line select the samt three items in reverse order “Delta, Charlie, Bravo” my filter would show two different options
I need the revesed input to look the same as the first input, always sorted in the right order (or alphabetical order)
Should be much easier for you to NOT select items in reversed order 🙂
You will have to split the list into an array and sort the array, then join back the array and write to cell.
Sorting an array is not a default vba method, but you can find workarounds like sorting in a temp sheet, or rewrite the array.
Hi, I don’t know much about VBA but absolutely need this function. I have entered your code and even named my range the same as per your example. Still no luck, it only makes one selection and no additional selections are added. I am not sure what I am doing wrong. I tried to ‘break’ it by referring to a named range on a different sheet – which I know this code is not designed to do – just wanted to see if anything actually runs – I get an error and have to debug (which I understand).
The process I used – Created the named range, created the drop-down list validation that points to the named range, copied the code exactly as per your example file and saved.
Could you please help me make the code work. Will appreciate it very much
Thank you
Hi Madelein
I’m happy to help but will need to see your workbook to make it work.
Please open a topic on our forum and attach your workbook to that.
Regards
Phil
Hi, I had a look at it with fresh eyes today and found my mistake! Key words – ‘intersecting ranges’ – I am all sorted. Thank you for your time
no worries 🙂
you can add the following code to disallow duplicate:
After OldVal = Target.Value
Add
If InStr(OldVal, NewVal) 0 Then
MsgBox “Value already selected”
Application.EnableEvents = True
Exit Sub
End If
Thanks Taleb.
Re Select Multiple Items from Drop Down (Data Validation) List:
I initially thought the application was going to allow a “simultaneous” selection of multiple items (like in a Autofilter criteria list), but this is an interesting, albeit not very complicated, way of achieving multiple entries in the same cell.
One thing I noticed that you may wish to mitigater is that the user can select the same item more than once, which I doubt is ever an intended option for this sort of DV.
Thanks Col.
The initial request that spurred me to write this didn’t need to prevent the same item being chosen multiple times, but I’ll include code to do this if people want to use it.
regards
Phil