There’s a popular sample file on my website, that lets you select multiple items from a data validation drop down list. Since the original article, I’ve posted updates:
- how to set up multiple selection
- edit multiple selection cells
- prevent duplicates in multiple selection column
And here is the latest update.
Remove Previous Selections From Cell
In the comments on the original post, Dan asked for a way to remove items if they been previously selected. For example, the cell contains four items — “One, Two, Three, Four”.
When you select Three again, that number is removed from the cell, instead of being added at the end of the cell.
Download the Sample File
To experiment with this technique, you can download the sample file from the Contextures website, on the Excel Data Validation – Select Multiple Items page. The file is in Excel 2003 format and zipped. There are macros in the file so enable them to test the features.
The new sample is on the SameCellAddRemove worksheet.
Please let me know in the comments if there are other features you’d like to see in this workbook. Thanks!
Watch the Excel Tutorial Video
This video shows how the multiple select and remove features work. You can also see the Excel VBA code, and the steps that it goes through, when you make a selection in the drop down list.
________________
Hi -Is it possible to have each menu item when selected appear on a separate line instead of separated by a comma
?
Thanks in advance,
Brian.
I have a quick question…I found out finally how to be able to add more than one selection to the cell, thank you very much for your help. However I have one last question about removing if you accidentally hit a selection that you do not want in that cell? So my question is even after watching the video, I am having difficulties. Can I combine all these codes into one worksheet that I need all these things to happen:
Adding more than 1 selection
Removing any additional selections I might have chosen.
How do I go about that adding all these codes into one worksheet, or do I need to keep them separate on their own worksheet, but then how do I get all these things to work on a spreadsheet column? Please help. Thank you so much.
How could I make the font style of the 1st selection to be (Bold) ” One, Three, Four” (one)to be Bold in this example . What is the suitable code for this to be added with the multiple selection drop down list code?
Also, what if i have more then two cells with drop down menu and i want that all my cells get a multiple selection too?
while all my cells can have multiple selection, can i have one cell with only one selection?
I am unable to get your workbook to work properly, specifically the SameCellEdit, but nothing appears to work.
I am an old hand at Excel and VBA and doing multi-select picklists is not something I would generally do in Excel. But sometimes Excel is the only tool at hand and it is all about imagining how it can be done. Worked like a champ; even handled the old multi-select data I downloaded from Salesforce.
Your tutorial sessions are fantastic.
Just wondered do you know how to add more than 1 SameCellAddRemove columns on 1 worksheet.
I need 3 columns with this samecell add remove feature on same worksheet/
This formula works brilliantly, thanks. Now my only problem is that it doesn’t work if the worksheet is protected, even if the actual cell containing the dropdown list is unlocked. It just behaves like a normal data validation list, i.e. the code is ignored.
Is there any way to allow it to work even if the sheet is protected?
Found the answer! Had to add this macro into ThisWorkbook:
Private Sub Workbook_Open()
ActiveSheet.Protect Password:=””, UserInterfaceOnly:=True
End Sub
Glad you found an answer!
This didn’t work for me.
i have the same issue and need to solve it please.
Help.
Hey Edwin,
Thanks for the code. I’m still new to vba, I cannot get the password unprotect and protect to work – can you share your code please, or how do I incorporate this code into the multiple drop down code?
Thank you.
Llewellyn
This formula/code works great, except when the list of items for the drop down contains common text across more than one item (e.g. list of items contains “Agree”, “Strongly Agree”, “Disagree”, Strongly Disagree”).
In this example, after selecting “Agree” and “Strongly Agree”, the cell where the drop down list exists is populated with “Agree, Strongly Agree”. However, when re-selecting “Agree” again to remove it (unselect it, if you will), the cell is left with “Agree, Strongl”.
Is there a way to get around this (unfortunately the options have to be the text listed above)?
@Ken, I’ve added a new worksheet — SameCellAddRemove2 — in the sample file, and it avoids the problem of removing similar entries.
The premium version of the technique also avoids that problem, by showing a list of the selected items. Just remove a check mark, to remove that item.
I don’t see the SameCellAddRemove2 sheet in the sample file? I downloaded from here http://www.contextures.com/excel-data-validation-multiple.html but it only had the original SameCellAddRemove sheet, which has the issue Ken Roberts mentioned above.
@Marcus, thanks, and the SameCellAddRemove2 sheet is back in the sample file now. Not sure how or when it disappeared!
till now i dont know how to make this function please i need easy way to make it .
thanks
@Debra, thank you very much for this “tool”. I downloaded the sample excel sheet and I was wondering how can I merge the code for “SameCellAddRemove” and “LineBreak”. I would like to use both functionalities.
Rodrigo, in the code for the SameCellAddRemove, change the “, ” separator to vbCrLf. There are 2 lines where that occurs:
Target.Value = Replace(oldVal, newVal & vbCrLf, “”)
and
Target.Value = oldVal _
& vbCrLf & newVal
Thank you very much, it work!!!
I really appreciate your time and assistance with this.
Hello there,
I’m using the code below and it works well. The code applies to the whole worksheet as I have multiple drop downs that I want it to work on. The only issue I have is that I would like to remove the ability to select one particular word on the drop down list alongside any of the other options. In other words, by selecting another choice this word no longer appears in the cell. (The word is “Select”). Many thanks for your help.
Private Sub Worksheet_Change(ByVal Target As Range)
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
‘do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
‘ NOTE: you can use a line break,
‘ instead of a comma
‘ Target.Value = oldVal _
‘ & Chr(10) & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Hi –
Im getting a run-time error anytime I change something in a cell outside the target column. Any thoughts?
That’s surprising. I would have expected the code
to only respond to that column. Confirm that your code has not been altered.
Can you post your error details here? (Err.description and Err.Number)
As with Edwin’s request, I’m trying to get this code to work on a password protected sheet. I am not getting it right with the code he provided – please help!!
Thank you.
Llewellyn
@Debra Thank you very much for the code. I’m now using it all over. I do have a couple questions though.
1) After the values are changed to “Choice1, Choice2, Choice3”, I’m getting a data validation error. Naturally, the dropdown has three individual choices, neither is “Choice1, Choice2, Choice3”. Must I suffer through this error or is there a way around it.
2) If I type in an item into the box, it will receive it as if I selected it with the mouse. It is case-insensitive though and I may end up with “Choice1, Choice2, choice3” or even “Choice1, Choice2, Choice3, choice3”. Do you have a workaround for this.
3) If I start to type in something and then click the dropdown arrow expecting to select an item, excel immediately tries to validate my partial entry and complains “Invalid entry, [Retry], [Cancel]. If I click [Retry], fine. If I click [Cancel] I get a runtime error “Method Undo Failed”. Is there some code that can avoid this issue?
I thank you again for sharing your expertise with the rest of us.
How do I use SameCellAddRemove code for more than one column? Ex. I currently have it being used for column 12 but I’d also like to apply the multiselection to column 10.
Thanks!
I am currently using the code that allows multiple selections in a cell. However, I am having an issue with the code. Whenever there is data selected in the cell and I double-click in it and then click out of it, whatever was in the cell disappears. This is a huge issue since I cannot undo the deletion of the data in the cell, and there is no other way to recover the data. How can I fix this?
Please help! Very urgent.
@Coder, in the sample workbook, use the code from the SameCellEdit sheet. If the “Edit Entries” box is checked, it stops running the event code. You can make your changes, then uncheck the box.
Debra, I am new at this and would like to combine several of these for use on a worksheet (not just a column). How would I enter the code to allow for multiple choices in a drop down, no duplicates, and a line break instead of a comma?
Candice, make the following changes to the code in the SameCellNoDups example:
1) Change this line, that uses the comma and space separator:
to this, which uses a line break:
2) Change this line, which limits the code to column 3:
to this, which allows the code in any column:
Following Candice’s comment, is there a way that I can get it to skip two lines ie. have a space between selections that fall one under the other in the same cell?
Secondly, is there a way to make amendments to the text that is added in the editable cell. Ie. I select something from the drop down in bold but then the text I enter is unbolded?
Just an update I figured out the spacing but would still like to know how to change the font and perhaps the height of the line break as well. Thank you
When I delete something that was selected in error and the click off of the cell it keeps the old selections and then doubles the whole thing with the edit done. So If I removed a drop down list that said Mark, Jason, David and David was selected in error and I deleted it I would get:
Mark
Jason
David
Shows as
Mark
Jason
David
Mark
Jason
How can I resolve this. If I try to take duplicates out it simply doesn’t work. I was able to get it to work but then I don’t have the other options. Please help with this.
In the sample file, on the SameCellAddRemove worksheet, there is an example that lets you remove items if they been previously selected. There is also a worksheet that shows how you can add an Edit option, and turn that feature on or off.
Hi Debra,
I am 100% new to this whole coding aspect of life. I have 2 questions. The first is that i’m having difficulty with the removal of an entry in the code. If I’ve selected multiple entries in my same cell and i don’t need one of them or it was accidentally selected, i can’t get the removal code to work. I’m positive i’m entering it wrong…
The 2nd question is am i able to set this to work on multiple columns in a single work sheet? So where we have entered “Target.Column = #” is there a way to apply this to say columns 8 and 2?
Hi Gemma,
There are examples here on how to add more columns in the code:
http://www.contextures.com/excel-data-validation-multiple.html#column
The Remove code is in the sample file that you can download from this page:
http://www.contextures.com/excel-data-validation-multiple.html
On the Instruction sheet, there is a list of all the example sheets, and SameCellAddRemove2 is #13.
The VBA code only works in one cell. What do you do if the column contains several drop down lists and you want to be able to select multiple values from each list?
The code should work for any cell that has a data validation drop down list. It’s not limited to just one cell.
Thank you for sharing this very useful tool. I have found success when I am just creating a data validation in a new spreadsheet. However, I am needing to use it in an existing spreadsheet, which has multiple worksheets, vlookups, and existing data validation. The main worksheet is in a table format. I thought that may be an issue, so I removed the table format, but it didn’t seem to make any difference. The spreadsheet is not locked either. Are there any issues when using an existing spreadsheet that already uses vlookups or multiple data validation lists? I have used the both the SameCell code and the SameCellAddRemove options. Both only allow the one entry. I have also made sure I changed the code to include the correct column. Additionally, I have tried adding code in for many columns and removed the code for columns to allow for any data validation columns. So far, no luck. Any assistance would be appreciated.
Hello,
Was wondering if you could assist please…
I have the below code in Excel from “Excel Drop Down Multiple Select or Remove”. The sheet is now protected and the code no longer works.
1) What line do I change to allow the multiple select to work.
2) I’d like a line break instead of comma and if possible, for the multiple items to appear in A-Z order (if possible).
Any suggestions?
Thanks,
James
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Set rInt = Intersect(Target, Range(“A:A”))
If Not rInt Is Nothing Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, 1)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = “dd/mm/yyyy hh:mm”
End If
Next
End If
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
‘do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) – 2)
Else
Target.Value = Replace(oldVal, newVal & “, “, “”)
End If
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
James, there is sample code on my site, for use with protected sheets:
https://www.contextures.com/excel-data-validation-multiple.html#protect
To use a line break instead of a comma/space separator, use Chr(10), instead of “, ”
There isn’t a simple solution for your A-Z question.
Hi Debra,
Thanks for your reply, I’ve managed to get the protection issue sorted.
Regarding a line break, when I remove an item that is already in the list, it removes a letter from the previous line, for example:
Selection 1
Selection 2
Selection 3
would turn into:
Selection 1
Selection
Many thanks,
James
Hi,
Is it possible that I want to be able users to choose multiple “Team” and show all Tasks under that Team; and at the same time be able to further choose multiple selection under “Task”:
Team Task # of Docs
A Editing Docs 3
A Proofreading Docs 2
A Final Draft Docs 0
A Printing Docs 2
B Editing Docs 2
B Proofreading Docs 5
B Final Draft Docs 6
B Printing Docs 0
C Editing Docs 3
C Proofreading Docs 2
C Final Draft Docs 2
C Printing Docs 0
Please help because as of the moment I have to input Team letter on each row, so that when I filter via the Team column, it shows the data on the following column