You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
Excellent. This is just what I needed. I actually extended this a bit so that the default entry on the list always says “pick from list”.
Next thing to add would be a feature that goes through the already created list of values from the drop-down list and exclude those values on the drop-down that have already been added.
Hello ingeno79, i have been trying to add a default entry. Would you mind sharing that code with me?
Thanks ingeno79, adding a default entry is a good idea.
To exclude the items that are already added, you could combine this with the technique in this tutorial:
Hide Previously Used Items in Dropdown
How do you select multiple items to more than just one column?
@Hadji, you can change the code to include multiple columns with Select Case.
There is sample code in my comment here:
http://blog.contextures.com/archives/2009/09/18/select-multiple-items-from-excel-data-validation-list/#comment-13540
How do I filter the column by individual results?
For instance if column A has the following cells:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
I want to use the auto-filter option so that if I filter to show only “orange”, I will get A2 and A3. If I filter to show only “grape”, I will get A2 and A4.
This works great, but I have moved the script into a new workbook and each time I open this new workbook and try to make a change in the dropdown list a warning comes up to say that the cell or chart is protected. I can unprotect the sheet by going to tools and protection, but each time I open it i get the same error message. I need to remove this automatic protection which seems to come into effect on open so that I can have other users use my sheet without getting warnings.
Your help would be much appreciated.
SP, there’s code in the ThisWorkbook module, that runs when the workbook opens. Perhaps you copied that to your new workbook too. If so, just delete that, and the SameCell sheet won’t be protected automatically.
Thanks Debra – works great now.
Debra,
Great work on the multiple item validation. I was wondering if there was anyway that I could change the macro for “LineBreak” such that I could limit it to a single or multiple columns but not the entire spreadsheet.
Thanks,
Steve
Is there any way that I can have more than one cell use this code instead of just column 3 as in the example?
Thanks,
Kai
marvelous, didn’t know it was possible.
it is exactly what I was in need.
thx,
Rafael
Works great, but….. One question. If you select an item in err, what is the best way to remove it? I went to the formula bar and deleted the duplicate item but it gives me an err ” The Value you enter is not Valid” “A user has restricted values that can be entered into this cell”
Also, when I select more than two items I receive the err ” The value in this cell is invalid or missing, ….”
Thanks for your help,
Juanita
Juanita, if you want users to be able to edit the cell, you can change one of the Data Validation settings. On the Error Alert tab, in the Data Validation dialog box, remove the check mark for ‘Show error alert after invalid data is entered’
I am trying to copy and paste the code for the same cell with comma separators to work for column T (which is column 20?) And it isnt working for me.
I even tried applying to the entire worksheet and deleting the lines with the red dots, but that isnt working either.
Any tips?
Thanks,
Cameron
I am a little puzzled as to how to use filters along with the multi select drop down lists to search or isolate particular values. My programming skills are very limited so I am having some difficulty working this out. Other users of my spreadsheets have very little knowledge of how it works so I need it to be as easy and foolproof as possible. Would it be easiest to used the column option or the single cell, and will the filter like this?
Hi Mary,
If you need to sort, you will need to separate out your items. It will sort by the first item it see in the cell.
Hope this helps,
Juanita
I am really a novice programmer and I can’t seem to get this to work for me. Can you please explain how to modify the code to apply the comma separators in the same cell to the entire sheet?
Any help is appreciated!
Thanks!
Cameron
Cameron, right-click on the CommaSeparated worksheet tab and delete the lines with the red dots. Then the code should apply to the entire sheet.
I am trying to use the code from the “Same Cell” tab. I am deleting the lines with the red dots and I can not get the code to run.
I am obviously doing something wrong, but I do not know what it is.
I am inserting a module in VB, pasting the code, deleting the lines with red dots (without re-formatting the code. Do I need to be deleting the blank lines where the code used to be and evening up the tabbing of the “End If”s) and then hitting run, naming it “comma” and trying it out in the sheet.
Can you point out if this is the wrong way to go about this?
Thanks!
Cameron
Cameron, the code should be pasted onto the module for the worksheet where you want to use the code.
So, if you’re trying to use this on a worksheet named “MyDataEntry”, right-click on that sheet’s tab, click View Code, and paste the code there.
I am new in excel, I am also trying to enter multiple dates in one excel cell but fail to do it, It will be very appreciated if some one help me in this regards. Please do it without VB.
hallelujiah!!!
it works!
thanks you, debra, so much!
Cameron, you’re welcome! Glad you got it working.
I have downloaded the zipped file but when I go to try the different tabs they are not working. When I copied the SameCell coding to my spreadsheet it worked great but I wanted to try some of the other methods and now none of them work. Help!
John, make sure to enable the macros when you open the sample file, and then the different tabs should work.
Thanks for your response. I must have glitched something because after I closed all my open Excel files and re-opened Excel and everything worked fine.
I need your help on some of the programming. I want to limit the programming to a specific column because in other columns I want to use a regular pull down menu. This is the first time I have ever enter any type of programming in Excel! What additional programming language would I need to use to limit the CommaSeparated, LineBreak and SameCell programming to a specific column (Column 10 in my spreadsheet)?
Thanks!
John, most of the examples refer to a column number, e.g.
If Target.Column = 3 Then
Change that column number to 10, and it should work the way you want.
Hi Debra, Your script on this multiple selection on drop down list is very useful. For the “SameCell” sample, one issue I encountered is when you selected a wrong entry and tried to delete it, entry can not be deleted but instead the cell will contain “Old value” + “new value”.
Sample:
Old Value = “One, Three” {then I want to delete “Three” by selecting “Three” and press Delete , then press Enter}
New Value = One, Three, One (which is the Old + New)
Hope you can help me with this bug. Thank you.
Grace, unfortunately, there’s no way to distinguish your correction from a selection in the drop down list. When you change the cell contents to “One”, that becomes the new value, and it’s added to the old value.
You could clear the cell, then select One from the drop down list.
Hi, I am able to apply this code to my worksheet just fine. However, when I select an item in error and I try to delete it from the cell, it actually adds another “wrong” item. The only thing I can think of is to clear the entire cell and start over with my selections. Is there any other way to work around this?
Thanks in advance
Hi Michelle, you can clear the cell and start over, or stop the event code from running while you make the change.
Dear Debra,
The examples are just great! Esp for those who don’t have programming skills like me (completely a layperson).
Currently am following the example – LineBreak. In my case, most of the pre-defined items are quire long (the longest has 249 characters so at least I have to set “110? width) in order to get a full display view from the drop-down box. I was wondering if there was a way to move the “selected items” cell down to next row instead of new column. i.e. Pick from C3 and the selected options to be displayed on C4?
Thanks
Hi Debra,
This is almost exactly what I have been attempting to accomplish. However, I need this to run on multiple columns, but not the entire workbook. Is there a simple way to do this?
For example; I want it to apply to all rows greater than 2 and all columns higher than C…
Thanks for the help!
weikee, you can change the code for the line break example, so the text goes into the cell below:
Michael, you can change the code so it checks the target’s row and column:
Hi Debra, It works perfect! Thank you so much again for sharing your expertise.
weikee
How can I make this work for Excel 2007?
Hi, this is fantastic. I am very novice and have a small problem. I used adding to the same cell macro and changed column to column 6. I also want to run the same macro but from column 8 in the same spreadsheet, but I get an error message. i have tried simply calling it a different name but obviously i am doing something wrong. I would appreciate any help you can give me. Keep up the good work, this is fab. Thanks, Anna
boeh, the macro should work in Excel 2007. What problem are you having? Did you enable macros?
Tinks57, in the code, you can change the line that mentions the column, to include both columns. For example,
These tools are really great. I cna see many great uses for these. How would I modify the code to get a combination of samecell + line break. I’m looking to have multiple selections show up in the selection cell and seperated by a line break (not the cell to the right of the selection cell)
Thanks.
This was a great help! I was able to start out with what you had provided. Then the above string answered some of the additional items/features I was looking for. Great resource, and thanks.
Hi Debra,
This works perfectly until I save & reopen it. When I reopen it reverts to one selection only per cell. I have copied the code from the same cell tab, replaced it with the appropriate column no & saved as macro-enabled. Any Ideas?
Thanks,
Peter
@Peter, make sure to enable macros when you open the file — there’s a security warning bar just above the formula bar.
If you don’t see the security warning bar, change your macro settings to “disable all macros WITH notification”. (Office button, Excel Options, Trust Center, Trust Center Settings)
Hi Debra.
A very silly question I know, but……….
The list does exactly what I want it to do. However, how do I apply my own (very long) list instead of the default one, two, three, four five?
Thanks very much.
I’m jump in in the middle of this thread.
Kudos for your Same Cell and Hide Previous routines. I have implemented the Same Cell (using comma separation) successfully, but my Hide Previous routine needs to work differently than the way you’ve implemented yours. I want to implement the Hide Previous the next time I click the drop-down in the SAME CELL and remove all previous choices used in THAT CELL from the available choices. This makes for a more effective pick list becuase there’d never be a need to pick the same choice more than once on a given day (each row).
When I get to a new row, my full list of available choices starts all over again.
COMMENT: I am not a VB programmer but I assume that there is not a way to open up a combo box of multiple choices and either control-click on any of them or click on checkboxes associated with each. That would get around all these INDEX, OFFSET and MATCH functions.
But cheers to you for figuring out how to do it and thanks for your many useful contributions.
Hi Nik,
Use a named range as the data validation list source, as described here:
http://www.contextures.com/xlDataVal01.html
That’s fantastic Debra.
Thanks so much for your help.
Nik
Hello. Thankyou for all your comments and videos i have managed to get things really rolling 🙂
My question please, is it possible to pick any number of names (for eg)in a drop down list without having to drop the list down again each time. Like a tick box against each item in the drop list. cheers
@Nik Vyas, you’re welcome, glad it helped you.
@Rachel Richardson, you can only select one item at a time with a combo box, but you could use a list box instead, and set its properties to allow MultiSelect.
Hello Debra. Have tried that but having difficulties, what i am after is selecting say 3 things from a list (check box ones wanted) and then the chosen options stay in that same cell spaced by a comma. Is this possible please.
Rachel, I don’t have an example of using a list box to enter multiple items in a cell, but I’ll try to get one posted in the next couple of weeks.
Hi Debra,
First of all Thanks for Ready to use downloadable excel file. It made life much easier.
Right now I’m using “Same Cell Add Remove Code” for Data Validation.
Is it possible if we can get check boxes to select or remove with bit more ease?
I’m working with Office 2007.
Looking forward to hear from U.
Thanks a lot
🙂
I have used the code and it works awesome. But can someone plzz help me with a code for the same thing to work on “Open Office”. The same code does not work. I have used it but connot implement it ‘cauz many systems in my office has Open Office so the sheet is on hold.
Debra – You are my hero!!! Thank you so much for this code, tutorial, thread posts, etc! I’m a complete programming novice, and it works! Next comes List Boxes!
Thanks a million!
Hi Debra:
I looked at your videos and they are awesome! Thank you! I however have a small problem. I am trying to create a spreadsheet that allows me to choose from a list (say apple, orange, lemon, grapes etc). I would like the output to be more than one selection. In other words, I would like it to allow me to select apple and orange and display it as apple, orange. I downloaded your example and it did not allow me to do that. Do you have a location where there is an instruction for that?
Fantastic! The comma delimited Data Validation just saved me loads of time! Thank you.
I do have a question. Is there a why to conditionally remove the comma? For instance, my list is:
EXCEPTIONS
Auto
General
OTHER
Liquor
Service
I would like a Colon to come after the All Caps selections, rather than a comma in the list so as to read as follows:
EXCEPTIONS: Auto, General, OTHER: Liquor
Is this doable?
Big thanks!!!
LB in GA
I am actually trying to fill out an excel document that has a list box in it. I am prompted to select all answers that apply, however, I can not figure out how to select more than one choice. Have tried everything I can think of. Please advise!
This script dosn’t work for office 2003 ? Any suggestions ? Thanks.
Adam, the code was created in Excel 2003, so should work in that version. Did you enable macros when you opened the workbook?
Hi Debra,
Great work!! Can you add ‘All’ in the selection list and when selected it will add all the values from the list? I have a use for this in a chart.
Cheers,
Myrna
Myrna, thanks! I don’t have an example that lets you select All, but it’s an interesting idea. If I put something together I’ll post about it on the blog.
Dear Debra,
The code is working perfectly. However, Is it possible to avoid to select twice the same items from the dropdown list ?
Dear Debra,
I have been looking for this in the past few days and is realy work for ppl like me who has no any programming background. Thanks thanks thanks!
It work perfect for me and I am able to apply to more than one columns and not entire sheet.
Cheers,
Fiona
Junior, I don’t have an example that prevents selecting the same items twice, but maybe someday!
Fiona, You’re welcome! Thanks for letting me know that the example helped, and you got it working perfectly.
Debra, thank you so much for this tutorial! I am using data validation drop down lists in many columns (i.e. yes/no) but I would like the Same Cell format code to be used for only columns 12 through 15. What change would I have to make to the code to make this possible? I know you answered an earlier question relating to this, but the question by Tinks57 was not regarding corresponding columns. Thanks again!
Debra – thank you so much for this tutorial! Excellent wealth of information here.
Using the “Separate Rows” example – is there a way to move the “selected items” cell down to the next row instead of a new column? i.e. Pick from G3 and the selected option to be displayed in G4?
Thanking you in advance – you are my “Shero!”
hi Debra,
I have the code working for my needs, but i’m trying to change the code so it only checks specific rows. Im using the Fill Down a Column code but would like to make it check only specific cells ie giving it a row and column check, but the code is different than in your examples, could to explain what i should add/change to get it to check a row and column in the fill down a column code?
Thanks
I’m having trouble making this code work for me. It is very unreliable. It will work one minute, but next time I open the worksheet – or try to use it in another workbook the exact same way, nothing happens. I’m saving everything and macros are enabled and they are in .xlsm format. I’m using Excel 2007 can this be why? Any ideas what I’m doing wrong?
Hi,
Thank you so much for this, it has really helped me.
Just a quick question, is there a way to stop duplicate selections. ie. if you have a list with “Rob,Bill,Ben,Steve” etc. and the user selects “Rob”, then “Steve”, and then tries to select “Rob” again, that it doesnt allow it as its already been selected?
Thanks so much
Debra,
No need to answer my question on October 25th – using the “Separate Rows” example. I figured out how to move the selected items directly underneath the data validation dropdowns (next available row), rather than the column to the right.
Thank you for the examples!
@Lisa, thanks for letting me know that you figured it out.
@Spencer, can you give an example or a bit more detail on what you’re trying to check?
@beth, it should work without problems in Excel 2007. Is there other code running in your file?
@Trevor, there’s nothing in this sample file that will stop duplicate selections. Something to consider for future version though — thanks for asking!
This has been so helpful; thank you for your valuable information!
I would like to echo Tom’s question:
“How would I modify the code to get a combination of samecell + line break.”
I too would like to have multiple selections show up in the selection cell and be seperated by a line break as opposed to a comma. Any suggestions?
I hope Debra doesn’t mind me answering but if you want a line break in the same cell, replace the line od code with the “,” on it with this:
Target.Value = oldVal _
& vbLf & newVal <<<——
That will give you a line break instead of a comma.
LB in GA
Debra,
Thanks!
What if i want to do data validation and run the same cell code for columns 6, 9, 12?
Troubled,
ELaine
Hi Debra and everyone,
One more question. What if i want to do same cell line break? How do i code that?
Thanks,
Elaine
Hello Debra! This is amazing, and almost exactly what I need. I am trying to target this code to one specific cell, and repeat it in multiple specific cells in a sheet. Can the code be changed from “column” to “cell” and put in parenthesis the comma separated list of specific cells? I’m not sure if that is even possible, but I wanted to ask.
I have created the file with datavalidation drop down list but while e-mailing the same the same is disappearing (code) from the file. The receipient also want to see the changes? Can someone help on this
@Terry, you could use Select Case and list the cell addresses, e.g.:
Where in the code do I paste this
Select Case Target.Address
Case “$A$7”, “$C$5”
‘code here
End Select
In order to add multiple columns for this coding function please? I have several and cannot seem to separate them by a comma.
Many thanks!!
@Dora, the Select Case…End Select would replace the If Target.Column…End If lines in the code.
HI Debra
Could you clarify the select case address- as copy paste this part into the macro did not work; could you point out with the full eg please
[…] where you need several cells with multiple options, you could use the technique for selecting multiple items from a data validation drop […]
Hi Debra,
This examples are awesome! Can you help me, Im trying to add the ‘SameCell’ code to this worksheet that already has code. I changed the target column to 14, but it seems to be executing the code on the other cells that have data validation as well. When it executes the code, it returns an error. The error is: “Compile error: Ambiguous name detected: Worksheet_Change”
and then it highlights the following code: Private Sub Worksheet_Change(ByVal Target As Range) of the code I copied.
Im wondering is it having problems with the other code thats on the sheet? If so, what do I need to change so it doesn’t execute on all the data validation cells, only on column 14?
This is the current code on the sheet I am using… Thanks a Million!
Private Sub Worksheet_Change(ByVal Target As Range)
Const NumQuarters As Long = 10000
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(0, -2).Value = Now
Target.Offset(0, -3).Value = NumQuarters + (Target.Row – 3)
Application.EnableEvents = True
End If
Application.EnableEvents = True
End Sub
‘ Job 2 —————————— WORKING ———————————————-
‘
‘—————————– Data Validation add in same cell ———————–
Private Sub Worksheet_Change(ByVal Target As Range)
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 Target.Column = 14 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Hi Alex. Once again, hope Debra doesn’t mind me answering but I saw this tonight and thought I’d help you out on the quick.
You need to take out your second reference:
Private Sub Worksheet_Change(ByVal Target As Range)
Just make it all one piece of code. You can’t have two references to the same Worksheet_Change Subcode. Make it like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Const NumQuarters As Long = 10000
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(0, -2).Value = Now
Target.Offset(0, -3).Value = NumQuarters + (Target.Row – 3)
Application.EnableEvents = True
End If
Application.EnableEvents = True
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 Target.Column = 14 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
See what that does for you.
LB
Wow! Thank you so much LB! It works great and thank you Debra for the ease you bring to us users to become better at Excel!
@LB, Thanks! Glad you were able to help Alex out with his code.
@Alex, you’re welcome!
[…] Select Multiple Items from Excel Data Validation List – instead of selecting just one item from a data validation drop down, you can select two or more. […]
Hello Debra, I know the Same Cell example was simply an example, but I liked it alot and I added it to a worksheet as you know. I get a little problem that maybe you have made a fix for it.
The code works great, but when I click on the drop-down list and for some reason click another cell in accident, it duplicates what ever is in the cell at the moment.
Example: cell A1 currently has
apples, pears, oranges in the cell
for some reason, when I activate the cell A1 with the text, and then drag my mouse away and click some other cell, it generates duplicate data of whats inside cell A1. So I end up with: apples, pears, oranges, apples, pears, oranges.
I un-checked ‘ignore blanks’ but it still happens.
Any suggestions?
Thank you,
Alex
Debra, Thank you for all your help! I got it to work but I have to click on the adjacent cell, then back to the cell in which I want to the data to appear for it to work. Is this correct? I am hoping to simply select from the drop down list for the item to appear without the additional step of selecting the adjacent cell and then click back. Am I doing something wrong? Thank you in advance for your assistance and Happy New Year! Best regards, Andrew
Debra,
Thanks so much for sharing this. It’s exactly what I need to assist (and impress) my colleagues. Outstanding programming. Keep up the great work.
M
Hi Deborah and Contextures Team,
Your tutorial and helpful details on the installation of the code ran very well for the ‘same cell’ multiple items data validation. I am so very greatful that you have taken the time to show us simple folk how to use excel in wonderful ways. I needed to use this programming code for my research project. Your help is greatly appreciated! Thank you.
Viktor.
For the total non-programmer…can you tell me how to get the “separate columns, separate rows, etc” tabs along the bottom of your worksheet? I can get to “view code” by right clicking the column number tab, but it just has:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Help for the least informed person on this blog please?
Jody
For some reason, the code isn’t working for me.
I’m using excel 2007.
Macros are enabled.
Any suggestions?
Nevermind. Once I closed and reopened, it worked perfectly. Thanks!!
Another question. I need to protect the sheet, so that some cells are locked. When I do this, the code no longer works. Would you have any advice?
Thanks
Hi Debra, and congratulations for your help for everyone.
I try to copy your “LineBreak” code, but the code is applying to every cell with a data validation.
(I think it comes from SpecialCells(xlCellTypeAllValidation))
I need that only on cells K5 and Q5 (even if I have a lot of other data validation cells in my sheet)
Could you help me to define these cells in your code ?
(I hope this is clear, I’m french…)
I am having the same problem as bernicerene:
“Another question. I need to protect the sheet, so that some cells are locked. When I do this, the code no longer works. Would you have any advice?”
can anyone assist?
thank you
Thom & Bernicerene:
You didn’t say which of the Tabs on Debra’s examples you were using so I assumed it was one that was unprotected from the start. I used Sheet 1, the Comma Separated Tab for this example. You can Protect your sheet (make sure your selection cells are unlocked), and still get the code to work by adding a With statement, like so:
With Sheet1
.Unprotect
ActiveSheet.Protect
End With
You are going to sandwich the code between the With Statement putting the first direction right after these two lines of code in Sheet 1, as follows:
Dim rngDV As Range
If Target.Count > 1 Then GoTo exitHandler
With Sheet1
.Unprotect
Next, you want to re-protect your sheet when the code is finished so end putting the End With code just above the End Sub, as follows:
ActiveSheet.Protect
End With
End Sub
Obviously, if you are working in another sheet, you’ll need to change the Sheet 1 to whatever sheet you’re working in. The end code remains the same.
Save, and try it. This worked for me in Excel 2003 & 2007.
Cheers,
LB in GA
Debra, Thank you for providing these solutions. I would like to use the LineBreak and LineBreakAddSort examples; however I would like to apply the codes to specific data valadation columns. Please suggest a solution.
Thank you again
Hi Debra , I have tried to delete error entries in the same cell dropdown list with comma separator but I get more entries from the dropdown list instead. Would you be able to advise me on this. Thank you very much
Lyle, the SameCellEdit sheet is the only one with code that uses the EditMode cell.
You could revise the code on the SameCell sheet, to also check the EditMode cell’s value.
This only works if you don;t have password protection on the sheet. If you do it asks for the password.
Angela, you can add a line of code at the top of the procedure, to unprotect the worksheet, e.g.:
ActiveSheet.Unprotect Password:=”abc”
Then, in the exitHandler section, reprotect the sheet:
ActiveSheet.Protect Password:=”abc”
Thanks for replying Deborah. The password problem is now solved, but has created another issue. When protection is re-applied,it comes back on with only minimum default permissions to select locked and unlocked cells. I need users to be able to adjust row and column sizes, insert hyperlinks and edit objects. Any ideas welcome
Angela, record a macro while you protect the worksheet, with all the settings that you want.
Then, add the password to the recorded settings, and put that in the multiple selection drop down macro.
For example:
Many thanks Deborah, that works a treat!
Sorry Debbie, too good to be true! For some weird reason this code is messing with date formats on the sheet. My employer needs them set to UK ie: dd/mm/yyy but even when cell formatting is correct (not system dependent)as soon as I apply the code, date fields revert to mm/dd/yyyy BUT only for dates where the day is between the first and the ninth; For example 1st September 2010 displays as 09/01/2010 but 21st displays correctly as 21/09/2011- remove the code and all is well?? Other date formats also appear the wrong way ie: dd mmmm yyyy
Code now looks like:
Option Explicit
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:=”hrmi”
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 Target.Column = 12 Or Target.Column = 14 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
exitHandler:
ActiveSheet.protect Password:=”hrmi”, DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingHyperlinks:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Application.EnableEvents = True
End Sub
I hope someone can help with this date problem as it’s driving me crazy!! I tried extracting MONTH and YEAR but these are also the wrong way round. I also notice a pile of odd date formats in the Custom format box, similar to: [$-F800]dddd, mmmm dd, yyyy ???????
Debra,
Thank you for posting this information. Unfortunately, I can’t get any of the code to work. When I download your example and try to use the multiple selection, it also doesn’t work. Is it possible this code does not work for certain versions or excel or with certain settings in place? I don’t even know where to begin to solve this.
Thank you,
Jenna
Hi all. I have solved my problem by formatting the date field as text and then using the MONTH and YEAR functions to extract month and year for filtering. Wierd but it works!
Thanks anyway for all your help.
Hi this is great, thanks
1) I have 2 questions. I am trying to alter the “SeparateColumns” code so that instead of the value going into the adjacent cell it goes into a column I first predetermine. For example I want the values from C3 to first go into D9 then go across as normal. D3 to start off in D10 etc.
2) Also can this code be modified so that the values are only placed in the cells if a macro runs. So I can make a button that the user presses and only then the values are placed in the cells? Thanks.
Tom.
Is there any way to add a line feed to the comma delimiter? I want to show the different values in the same cell, but in same cell column format. Simply put, I want an “Alt Shift” after each comma or better yet, replace the comma delimiter.
Debra,
Thank you for all the help in the tutorials and the above thread.
In the SeperateRows example, how can I keep the most recent input in the data valuation dropdown cell from being diplayed? (I want H1:H3 to show, but not G1)
Mark
@Phil Culver:
Go to the vba code and replace
this line: & “, ” & Target.Value with this line:
& vbLf & Target.Value
You may want to make sure that the cell you pick from is formatted so that the choice stays in the middle of the cell or at the top. If you don’t, it looks a little funny tracking at the bottom of the cell, because it too, get’s larger along with the data output cell.
LB in GA
LB in GA, Thank you!
Hey LB,
I would be happy if you or another person in this “thread” would like to help me instead of Debra.
Thanks to all of you,
Mark in SC
@Mark, at the end of the code, you could clear the contents of the data validation cell, by adding this line of code:
Cells(lRow, lCol + 1).Value = Target.Value
Target.ClearContents ‘< =====THIS IS THE NEW LINE
End Select
@Mark — you can try the line of code that I just posted in your original question
Debra,
Thank you for your quick and helpful response. (I was not well-informed on Blog etiquette, and I won’t ask others in this string for help as they can start thier own Blog.
I have created a worksheet based on your “seperate rows” worksheet. Then each selected object has a dependent list using the indirect command and naming the dependent range name the same name as the object selected in the first dropdown. Sorta like the “DataValRegionCust” ex. and using range names of “Ontario”, “Quebec”.
I wanted to make this work in a List Box (ActiveX Control) so that I could use the 1-fmListStyleOption and
1-fmMultiSelectMulti so that I can pick several objects at once in the first list and have them output the same as I have working. **I get #NA as the output**
I am attaching my code that works with the simpler dropdown list.
Option Explicit
Private Sub ListBox1_Click()
End Sub
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column ‘column with data validation cell
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
If Target.Value = “” Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 1, 12, 14, 18
If Target.Offset(0, 1).Value = “” Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 1).Value = Target.Value
Target.ClearContents
End Select
End If
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 Target.Column = 3 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& ” ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
“Select Multiple Items from Excel Data Validation List HELP”
Even though I am aware that I am able to view the code, but it is useless to me considering I know absolutely nothing about VB and/or Macros. Could someone please explain to me in “detail” and “step by step” on how I would go about getting this done? Please explain it to me like I am a stupid five year old. I will not be offended at all. Thanks ahead for the help.
If there are more than one selection in the drop down list that is able to be selected, will the “countif” function still work? For example, I choose from the list A,B and C in one row. I still want the function to still be able to recognize how many instances of A,B and/or C has occurred. Hope that makes sense. Thanks.
George….your not alone.
I to am in the same boat. Debra – How about some detailed instructions for us non-programmer types. The funtions your codes performs seem to be great but it appears it is more complicated than doing a copy-paste with the code.
In my case I want to use the “samecell” code.
I have a column of cells that already have data validation that use a list (drop down list) for them. I copy/paste the “samecell” code to the sheet, changed the column number to match my column, closed and returned to Excel, made sure macro’s were enabled and got nothing! I even saved the file as a macro-enabled workbook (.xlsm) which did nothing. Closed and reopend and still nothing.
PLEASE……help us. 🙁
Debra,
I promise to buy your book (everyone probably will), can you helh the above me with the Multiselect question? And, are there these kind of examples in one your books? (I believe you have several).
Thanks,
Mark
Mark, a ListBox uses completely different code than what we’re doing here, with data validation.
For examples, see Dick Kusleika’s post on Multiselect Listboxes:
http://www.dailydoseofexcel.com/archives/2004/04/27/using-multiselect-listboxes/
Rick and George, I’ve recorded a video on setting up the data validation drop downs, and adding the worksheet event code.
It will be posted on the blog tomorrow, so I hope that will help you.
Many Thanks..very useful! 🙂
Help. I got the same cell (comma separated) to work. Closed my spreadsheet. I have reopened teh spreadsheet and its disappeared. I’ve replicated what you have said but can’t get it to work.
Debra
Thanks so much for the new video. I did get mine working but have encountered a new problem. All the sheets in my file have password protection. Without protection the code works great. When I apply a password it do not function. Is that the way it is suppsoe to work or do I need to change something?
thanks
@Rick, make sure that the data validation cells are unlocked, before you protect the worksheet.
In the code, you can add a line at the top, to unprotect the sheet — include a password, if you used one:
Me.Unprotect Password:=”LOckEd”
In the ExitHandler, turn the protection back on:
Me.Protect Password:=”LOckEd”
Debra Dalgleish
May 1st, 2011 at 2:45 pm
@Rick, make sure that the data validation cells are unlocked, before you protect the worksheet.
In the code, you can add a line at the top, to unprotect the sheet — include a password, if you used one:
Me.Unprotect Password:=”LOckEd”
In the ExitHandler, turn the protection back on:
Me.Protect Password:=”LOckEd”
Debra
As regards the above I added the code as instructed (see below) but it still doesn’t work. The data validation cells are unlocked. If I use “LOckEd” as the sheet password it launches a pop-up window that asks for the password when I go to a cell (in the column defined in the code). If I use my normal password for the sheet I do not get a pop-up window but, the code does not work at all…..it just lets me enter one selection in the cell.
Am I still doing something wrong? In case I wasn’t clear before I must have the sheet password protected to prevent users from making changes to formula’s and other specific features…..for instances the list of entry selections for the cells the code is trying to control.
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect Password:=”LOckEd”
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 Target.Column = 10 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
exitHandler:
Me.Protect Password:=”LOckEd”
Application.EnableEvents = True
End Sub
Hi, thank you for the tips above. Is there any way I can make a sum of each of these:
For example:
Line 2: red shirt, blue shirt, green shirt
Line 3: blue shirt, red shirt
Total: 2 red shirt
2 blue shirt
1 green shirt
Thanks!
Hi Debra , I need to make changes to my drop-down list(either to remove or add on),so how do I go about to do it?. Thanks.
i want to have dependent combo fill how can i get that
like in same example of i need on selection of month name i need list of days of that month
please do ghelp
thanks in advance
I am using Excel 2007 and can get the code to work, however when I save the file I get a msg indicating I cannot save the following features: VBA Project, in a “macro-free” workbook. I gives me an option to save as “macro-enabled” workbook however the file type changes to XLSM. Is that the correct way? Will that work for users that have Excel 2003? Thanks…r
@Robert, you can save the file as Excel 97-2003 format, and people with Excel 2003 will be able to use the file too.
I am using the SameCell code and it works but I need to sort the completed input within the cell, so instead of getting orange, apple, pear; I need to see apple, orange, pear regardless of the order in which the user selects the item. Can you help? Thanks!
I am using the following code to select multiple items from a data validation cell. It works but I need to sort the entries within the cell so that instead of getting pears, apples, oranges, the result would be apples, oranges, pears, regardless of the order in which the user selects from the dropdown list.
It would also be nice (but not necessary) to check for duplicates.
Can someone PLEASE help? I need urgently. Thanks!
Option Explicit
‘ Developed by Contextures Inc.
Private Sub Worksheet_Change(ByVal Target As Range)
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 Target.Column = 3 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Hi there, thanks for this tutorial is really helpfull and I apreciate you’re sharing it with us, but I just have a question. I have a table that uses macros to add a new dropdown list each time you click on a button, I have at first four lists to chose multiple products but then I have to add more dropdown lists with the button, the problem is that the new dropdown lists don’t have the same ability as the others of selecting multiple choises in the same cell.
How can I keep the multiple selections each time I add a new list with the button?
Greetings from Mexico, Thanks
Hi, I am trying to duplicate step by step your “Select Multiple Items from Excel Data Validation List” using Same Cell.
I downloaded you DataValMultiSelect spreadsheet thought I could just copy and paste your code into a tab on my spreadhseet but cannot do it. Maybe I am not allowed to. That is ok. Is there a way to duplicate this easily. Do I have to pay? That’s ok too. I am running Excel 2007
@Stephen, there is another video that describes how to add the code to your workbook, and adjust it, if necessary:
http://blog.contextures.com/archives/2011/04/29/how-to-set-up-multiple-selection-excel-drop-down/
@Luis, you might need to change the code so it looks in all columns for the data validation drop down lists. This video shows how to modify the code:
http://blog.contextures.com/archives/2011/04/29/how-to-set-up-multiple-selection-excel-drop-down/
Debra
Thank you for this video. It works!
Follow-up question:
What if we wanted the same type of code listed within the same spreadsheet for column 3 and perhaps column 4 etc…
In other words, with your code, column two accepts multiple responses, can column three, four and five do the same within the same sheet?
@Stephen, yes, you can change the code to work in multiple columns. Here is the revised “If Intersect” section:
So Awesome!! I figured it out, of course, with the help of copying and pasting. Now, I wanted it listed with line breaks instead of commas. I can’t find an answer 🙁
I’m having trouble applying this code. If the columns I want the code applied to are 8, 10, and 12, do I specify that in the line that curently reads “Case 3, 4, 5”? That’s what I have tried to do but Excel isn’t accepting the code like that.
When I try to manipulate the code for my project, I get an error message, followed by highlighting of the line “Private Sub Worksheet_Change(ByVal Target As Range)”. Any futher advice on making this code work for me?
Charles, do you have two Worksheet_Change procedures on the sheet? You can only have one with that name.
This code did not work. I copied directly in to the visual basic and nothing changed. Any thoughts?
Hi Debra, Brilliant Macros, very well explained and video. I am using the ‘SameCell’ Macro and have amended the columns to the ones that I need.
Just wondering if you have come up with a solution for the following, I know they have been listed & asked for before, but thought you might of solved the problems :
1. Selecting multiple items from a list without the drop down disappearing each time you pick a item from a list.
2. Ability to edit the cell so that if you want to delete or unselect a item from within the cell that may or may not be within the list it doesn’t add the new values and keep the old values as well.
3. Ability so that if you have already selected a item from within the list if you try & select it again it tells you or even better still any items that have already been selected are listed at the top of the list and are highlighted.
Thanks
Keep up the fantastic work
Just found another request, when you click on the drop down list is there a way that the whole list is shown and not just 8 items with a scroll down bar.
Thanks
@Greg, thanks, I’m glad you like the article and video. I don’t have a solution for 1 or 3, using this technique.
For #2, there is a post that explains how to set up an editable sheet: Edit Multiple Selections in Excel Drop Downs
Thanks Debra,
That worked great for #2
If you ever find a way to fix the other questions that would be fantastic.
Debra
Thank you so much for your code. You have been a tremendous help to me and I appreciate that.
You vid was excellent and very clear to understand.
Thanks again!!!!
@Stephen, you’re welcome! Glad it helped.
Many thanks ….. your macro has been a great help, I appreciate you sharing it. Is it possible to limit the multiple selection to specific cells, rather than to the whole column? If so how?
Hello, i am using your samecell technique (Three, Two, Three, Four) from a drop down…this was an awesome find…thank you! but my problem and question is this technique then screws with my formatting and printing of the spreadsheet…so my question is …” is there a way to have them NOT be comma seperated but rather on vertically in the same cell ?”
Thanks again!
Dave
Very sweet code. Thank you so much!!!!! Full credit given.
Thank you so much for publishing both the multiple select codes as a zip and doing the video. I am completely unexperienced with programming and this has gotten me so much further than I would have been able to get without your assistance. I’m working on a huge worksheet that will have multiple single select drop down lists (I can do that), several same-cell multiple select drop down lists (thanks to you I can now do that), and several same-cell multiple select add-sort drop down lists. The last is where I’m getting stuck. I want to make selected columns function like the LineBreakAddSort but so they display in the same cell like the SameCell function does. I have figured out how to change from comma seperated to line break and back so that is not the problem.
I’ve been fidgeting with it and have it where on my sammple (not the final worksheet) I can get 3 separate columns to each allow multiple selects from their own lists and to allow write in contents. I could live with this if needed. But what I want and have not been able to get to work is to make the selected columns (not all) so that write in selections are written to the lists and the lists are sorted.
Below is what I have now. This runs but does not add the write ins to the lists. Where did I mess up??
Thanks so much to any and all for any assistance and for making this resource available. Pat
‘ Developed by Contextures Inc.
‘ http://www.contextures.com (change by Pat based on samecell)
Private Sub Worksheet_Change(ByVal Target As Range)
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 Target.Column = 2 Or Target.Column = 3 Or Target.Column = 6 Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
Application.EnableEvents = True
‘Dim rngDV As Range (commented out because it threw an error)
‘If Target.Count > 1 Then GoToExitHandler (commented out because it threw an error)
Application.EnableEvents = False
Dim ws As Worksheets
Dim i As Integer
Set ws = Worksheets(“List”)
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
If Target.Value = “” Then GoTo exitHandler
‘add new items to the list
If Application.WorksheetFunction _
.CountIf(ws.Range(“NameList”), Target.Value) Then
‘do nothing
Else
i = ws.Cells(Rows.Count, 3).End(xlUp).Row + 1
ws.Range(“C” & i).Value = Target.Value
ws.Range(“C1?).CurrentRegionName = “NameList”
ws.Range(“NameList”).Sort Key1:=ws.Range(“C1?), _
Order1:=xlAscending, Header:=xlGuess, _
OderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
How can I get the results to come up 2 columns over? I finally got it to work, but I would like to have a column between the list and the results. Thanks!
The code works perfectly as long as I don’t try to protect my worksheet. I removed the code on the “ThisWorksheet” tab but I am still unable to protect the sheet. Any help would be greatly appreciated.
TB, make sure that the data validation cells are unlocked, before you protect the worksheet.
In the code, you can add a line at the top, to unprotect the sheet — include a password, if you used one:
Me.Unprotect Password:=”password”
In the ExitHandler, turn the protection back on:
Me.Protect Password:=”password”
@Riles, if you’re using the SeparateRows example, which puts the selects in the column to the right, you can change this line in the code:
If Target.Offset(0, 1).Value = “” Then
to this
If Target.Offset(0, 2).Value = “” Then
Thanks Debra
The works great something exactly what I was looking for. but as I had to change this part of the code: “If Target.Column = 10 Then” (Note I am using this code only for columns 10 (J) – columns 17 (Q)
but the problem is if column 10-17 (J-Q) are blank and column 18 (R) has data in it, when I choose the drop down to fill my columns (J-R) any other column that has data after column R gets filled with the data that is supposed to fill columns J-Q.
For example column J-R are empty and column S has data then the drop down of column J will fill column T and so on, with the data that is supposed to go in column J-Q.
How can the code be modified to specify that this is only to be used on Target column 10-17.
But I love it. It works great. Wish we could modify it.
I’m sorry but I am using the
Select Multiple Items From the Drop Down List to Fill Across a Row
with its associated code
Is it possible to use two set of codes in the same sheet because i want to use the samecell and the separate column in my workbook on the same sheet.
Can more than one of the be used on the same workbook and on the same sheet? How do I put the code on the same sheet?
@KJW, you can change the iCol code, so it starts from column S, and looks to the left of that column:
iCol = Cells(Target.Row, “S”).End(xlToLeft).Column + 1
@KJW, you can combine the two options within the same Worksheet_Change code. Use Select Case, as in the Separate Rows sheet code.
For example:
Select Case Target.Column
Case 8
‘code for same cell
Case 10
‘code for separate columns
End Select
Thanks so much for this. It is just what I have looking for. Please keep up the great tutorials on vba. This is great. You are the best!!! Thanks so much again.
I’m sorry but I am not following what to do here to put the Samecell and SeparateColumn codes on the same worksheet.
And is it possible to make the Samecell code work with multiple columns within the same worksheet?
Ok looks good but I am more inclined to use SeparateColumn on two columns in the same worksheet. Now would I just have to modify this part of the code to read
If Target.Column = 10 or If Target.Column = 43 Then
column 43 is column AQ
How would I modify the the iCol code to continue to include the previous code for column 10 AND also to say start from column BC, and looks to the left of that column
Please help. I have been trying to modify the code based on information in this forum but to no avail.
First I would like to use the Separated Columns in two places on my worksheet column J (10) and column AU. I have tried to modify the code:
If Target.Column = 10 or If Target.Column = 43 Then
That part worked but I would like to modify the iCol code, so it starts from column S and BH , and looks to the left of those columns.
This works great but I just need a little help with the code. I have been trying to modify but not sure yet. Please help
@KJW, you can use Select Case, and two additional variables, to set the columns for the search to the left:
Firstly: thanks for an excellent code! Secondly: something’s terribly wrong out there… I use Excel 2010 and SameCell code that is working perfectly as long as I myself open the file (saved in .xls format). Why, oh why, it does not work for anyone else opening the same file? The list and the data validation works fine but no multiple choices possible. Not on Excel 2007 nor 2010. Could you help, please?
@rmk, can the other users run any other Excel macros? Perhaps their security settings are High, and that’s blocking the macros, without any warnings, on their computers.
Debra,I am not a programmer. This is the first time I am using the macros. I am not able to change the values( one, two, three, for, five) in the SameCell sheet. Could you please let me know where in the code I should go and change the values(list of drop down). thank you.
Hello Debra,
I have downloaded the workbook and tried using it before even copying it to my sheet and it does not appear to be working on the workbook I downloaded. The one I am interested in using is the fill down a column option.
Thanks for your help,
Heidi
@SAC, select the cells with the drop down lists.
On the Excel Ribbon, click the Data tab
Click Data Validation, and you’ll see the list of items in the Source box.
You can change the list there, then click OK to save and close.
@Heidi, you might need to change your macro security settings, and then allow macros to run in the workbook.
There are instructions here: Excel macro security settings
Debra, This is absolutely fabulous! Thanks.
I would also like to have the ability to REMOVE an item from a comma separated list. For example, in your workbook, on the SameCell sheet, I have a cell populated with “Two, Four, Three, Three”. This happened because I selected ‘Three’ from the dropdown list twice. I’d like the behavior altered such that an option in the dropdown list can occur once and only once in the target cell. Should an option be selected; if not present in the target, it should be added, if it IS present, it should be removed. Can you help?
Hi Debra !
Your code is exactly what I was looking for !, so firstly thank you for that. I was wondering if you could tell me how I can change the code below so that I can change the location of where the data validation cell is and where the data in separate rows is entered. For example, I would like the data validation cell to be somewhere in Column G and the data appearing in rows somewhere in Column B.
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
1 Col = Target.Column ‘column with data validation cell
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
If Target.Value = “” Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 7, 12, 14, 18
If Target.Offset(0, 1).Value = “” Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 1).Value = Target.Value
Target.ClearContents
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
Thanks !
Hi Debra – first of all let me thank you for all the wonderful scripts etc. I have found them very very useful. Over the weekend I was using your multiple drop down menu script which was exactly what I needed and it worked beautifully!
However, like one of the posters in this blogg (Angela), I found the date column was reversing the date format when the script was used – removing the script put the date column right again. Instead of dd/mm/yyyy, it would convert it to mm/dd/yyyy. I tried Angela’s workaround but it did not work for me.
The solution that worked perfectly was very simple. I inserted a new column next to the offending date column, copied and pasted the info into the new column then deleted the origonal one.
Everything works fine now – brilliant!! Presumably, whilst applying and configuring the worksheet something became corrupted.
I thought I would mention this as there may be others who have the same problem as Angela and myself have experienced and this quick fix might do the trick for them also.
Again, thank you for all your clever scripts and advice on this site.
Is there anyway to just allow the code to effect an individual cell instead of a the whole column. I’m building a data entry form and have many different validations within a single column.
Thanks,
[…] 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 […]
This is ideal for what I have been trying to do. Thanks very much.
One slight query would be that if I then filter the columns is there any way of just having the original drop down selections appear rather than the new, multiple selections I create using this solution.
As an example, if I was classifying fruit and had the drop down options green & round I would use this solution to give an apple the classification ‘Green, Round’ whereas a pear would just be ‘Green’.
When I go to filter this using the normal column drop down filters, the options I get are ‘Green’ , ‘Round’ & ‘Green, Round’. It is this last one I wish to prevent from seeing.
i realise this is above and beyond what you have tried to achive in this bit of code but just thought Id ask 🙂
Thanks
I came across your code and it works wonderfully. Thank you. I am using the code which allows multiple selections from a dropdown list separated by a comma in my worksheet. This works fine as my worksheet is unprotected. I need to have this functionality (protection) because I have formulas that I want to hide and cells that I need to lock. Anyway to get these requirements working together? Thanks in advance!
hello,,
I have downloaded the workbook and using it before copying to my sheet and does not appear to be working on the file. The one I am interested in using is the fill down a column option. but i use vba with excellent. but it’s problem now..
you can help me please..
thankou..
tamer nagah elasid ali .,
+2 01004611058
Hi
I have used the following code but am trying to get it so that I can delete one thing in the cell rather than the whole record.
How do I do this?
Private Sub Worksheet_Change(ByVal Target As Range)
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 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 _
& vbLf & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
i want to add up items from a drop down list in another worksheet but only if the item is selected from the drop down menu,for example: a1= 1S a2 = 2, i want a1 in the next sheet to say 1S and a2 to say 2, but if a1 = 2S then i want a1 in next worksheet to put a extra row in to accomidate the selection 2S
The file won’t download anymore…
@Amar, thanks, the link should be okay now.
Debra, I have used your SameCellAddRemove macro in a worksheet, to allow users to select/deselect multiple options from the drop down boxes. I would like for each selection to show up within the same cell, but on a different line. Can you please tell me how I would need to modify this code? In addition, I’d like for this code to only be applied to columns 4, 7 and 9 if possible.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
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 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
exitHandler:
Application.EnableEvents = True
End Sub
I am attempting to use the code from the “LineBreak” worksheet and I’ve copy and pasted it, but nothing happens. I have fairly long entries in my Data Validation list. I have named my list “InterventionChoices” and all cells in column 5 have this drop down list. I’m not sure how to change the code so I can add multiple selections from the drop down to the neighboring column with line breaks.
Hello Debra , my existing excel spreadsheet was in excel 2002 and lately my “office applicaion” was upgraded to version 2010. After which the “Select Multiple Items from Excel Data Validation List” does not work even though the code was intact. Your advise is much appreciated. Thank you.
I work on a Mac and therefore have Excel 2008 for Mac. I can’t seem to use your tip to cut and paste the code to allow for multiple selections in data validation because when I right click the tab in my worksheet, there is no ‘view code’ option. Is there another way to do this in Excel 2008? (Note, my knowledge of Excel is extremely limited.) Thanks.
@Lori, as far as I know, you can’t use programming in Excel 2008, so the multiple drop down code won’t run in that version.
@Lyle, in Excel 2010, you might need to change your security settings, then enable the macro when the file opens. There are instructions here:
http://www.contextures.com/excel-macro-record-test.html#ReadyTest
http://www.contextures.com/excel-macro-record-test.html#Test
Hi, is there any way in which I can lock the cell, I don’t want users to write anything, I want them to choose from the drop down list.
Debra!
This is EXACTLY what I need to do for a project of mine. I have a column of dealers and a column of brands that these dealers carry. I want the brands column to be a list where I can select/deselect multiple from 13 different brands. So Joe carries brand x, Tom carries brand x-y-z, and Tim carries x-z.
Here’s the catch…
I’m on Excel 2011 for Mac! 🙂
I know VBA is present in this version but I have absolutely NO idea how to integrate your technique. Help!
Thanks!
Hi Debra!
Your code is sensational and it’s made such a difference to my spreadsheets. But I’ve got this one niggling error: I’ve created a spreadsheet for colleagues to enter call data. One of the colums has a drop down menu with 3 options – the options are drawn from a separate spreadsheet (‘List’) and I’ve used a named range (‘callback’) so that eachtime I refer to that drop down list, it’s the same spelling, etc, and I can add new options easily.
The problem is that whenever people type something different into that column and then try to edit it, or try to start a paragraph on a new line, it doubles what they’ve written.
For example: ‘yes.no.blah’, and then the person tries to edit it, or presses ALT+ENTER, then it becomes ‘yes.no.blahyes.no,blah’.
Here’s my code:
Thanks!
Hi Debra, thanks so much for your code. Previous to using your code I had used the “Excel Data Validation — Hide Previously Used Items in Dropdown” from the Contextures.com site and it was working fine. Then I added your code and your code works great but now the other code doesn’t work at all. I’d like to combine both yours and the other one so that I can select multiple items in the same cell and then those items are no longer available to select in subsequent cells.
Any ideas?
Hi Debra,
I’ve use your “same cell” code and now I need to sum the count in the multi items cells. I tried using sumif, countif, but it all seems to only count the 1st item and ignore the other selected items in the same cell. How can I do this?
Thanks.
Anna
Anna,
I am having the same problem you are. Have you had any luck in figuring out what to do with it only counting the first item?
Juli
Thanks so much, Debra. I’ve created a database and this is just what I wanted. Been searching the Net all around & found you. It didn’t work at first because I needed to close the file & open it again, (+ the macro thingy). Guys, Debra is gassss !!! <3
@Blanca, thanks! Glad you found this page, and got it working.
I am having an issue understanding the coding behind the same cell tab. I am tryng to create a drop down box with 6 selections. I created it using data validation and it works fine. Now I am trying to set up that drop down box so that my users can choose more then one selection and the results will be listed in the same cell separated by a comma. I copied the data valaidation down through several rows for I will need my user to make their selections for every applicable row. I looked at the sample sheet, the coding and other articles and still can’t see how to get this result. Can someone clarify for me what exactly I should be doing? I am currently using Excel 2007.
Thank you so much for this code, Debra. While I’ve done a lot of coding for Microsoft Access in Visual Basic, I’m just getting started learning how to do so in Excel and your entry here was a real help.
I do have one question, though. Let’s say I have two spreadsheets:
Faculty Spreadsheet
Course Sections Spreadsheet
On the Faculty Spreadsheet, each faculty member has an ID number (see below)
Faculty Spreadsheet:
Faculty Name; Faculty ID
Mickey Mouse; 10111
Donald Duck; 10112
Uncle Scrooge; 10113
Launchpad McQuack; 10114
On the Course Sections Spreadsheet, I’ve set up column 2 so that it will pull the faculty names using your code. What I’d like it to do now is have column 3 pull in the Faculty IDs.
Below is an example of how I want the end result to look. Any ideas?
Course Sections Spreadsheet:
Section; Faculty; Faculty ID
ENG-101; Mickey Mouse; 10111
ENG-102; Donald Duck, Uncle Scrooge; 10112, 10113
ENG-103; Donald Duck, Launchpad McQuack, Mickey Mouse; 10112, 10114,
10111
Hi Debra.
Your code is awesome! I used and modified your csv separated example to my needs and it’s almost perfect now. Even the protection is working. Now, as my output cell is protected, the user cannot alter the content of this cell. In your sample “SameCellAddRemove” you can remove entries from the output cell by re-selecting the wrong one. How can I use this on the CSV seperated script?
Thanks so much!
AlexH
I really appreciate this VBA. I’m totally new to it, but just copying and pasting and a quick edit of column numbers and it worked!
Now, wondering if you can tell me how I can compare two cells that are populated by selecting items from the same list, albeit perhaps similar OR different items? May not be in same alphabetical order based on when selected. Would be much appreciated.
Hi Debra,
I used your code to enable selection of multiple values in the same cell, and it worked perfectly thanks..
But I’d like to be able to protect other cells in the worksheet, at the moment this stops the VB running so I can only select a single value again. Could you give me any tips on how to modify the code so that the VBA still runs when the sheet protection is turned on.
Thanks so much.
@MarkJ, at the start of the code, you can turn off the protection, then turn it on again, at the end of the code.
For example:
ActiveSheet.Unprotect Password:=”yourpwd”
‘code here
exitHandler:
ActiveSheet.Protect Password:=”yourpwd”
I’ve tried the code above for turning off the protection and turning back on again in Excel 2007, but it doesn’t work. I get the message “invalid outside procedure”. I’ve tried it exactly as above, with my password, with and without quotations. Any additional thoughts?
Your walkthrough and example worksheet are great!~ Very much appreciate all the effort you have put into helping everyone!~
I have tried the code change LB suggested above for having same cell line break.
I replaced:
Target.Value = oldVal _
& “, ” & newVal
With:
Target.Value = oldVal _
& vbLf & newVal <<<——
And it is not working the code turns red.
Any help is GREATLY appreciated!~
@Amber, your code looks fine, assuming you don’t really have the arrow at the end of the line. Do you have a space before the underscore character?
I am unable to edit the zipped zile to have the functions work in my spreadsheet. Even if i copy the code and delete those two lines to enable the whole document to select multiple items in a single cell (from the val. list), it fails to let me select more than one item? What is wrong? I am crunched for time and really need this function on a business trip and it will save me so much time.
thanks.
Hi Debra,
This is excellent – thank you so much for this tutorial – it is extremely helpful.
I have one question, I have made my own lists and named them etc but I can’t find where to change the list in the VBA code.
I look forward to your response.
Hi,
I’ve copied the VB Code. It worked but then, it stopped working. I don’t understand why.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:=”Form_”
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 Target.Column = 12 Then
If oldVal = “” Then
Else
If newVal = “” Then
Else
Target.Value = oldVal & “, ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
ActiveSheet.Protect Password:=”Form_”
End Sub
————-
I don’t understand why it is not working. And by the same time, I find the cell becomes crowded when the person will be selecting more than 2 so my question is: Is there a way to make the cell expend by itself when the content is to big?
Thank you
Nancy
Debra, forget my last message. I’ve seen that you had an example of the one with the line break. I juste downloaded it. Copied it into my worksheet VBA code but… nothing happenned. Is there something I should be changing?
Thank you
Nancy
@Nancy, make sure you have macros enabled in the workbook.
Hi Debra,
This blog has been very helpful to me so thanks.
Secondly, I have gone a little beyond just using the data validation list box and instead used a multiselect activeX listbox with checkboxes to select the items that I want stored, comma delimited, in the active cell. This allows me to select and deselect the items I want stored in the cell all at once. It all works fine until I go to edit a cell that has already got data stored in it, as when I open the listbox all of the items are deselected (if I were to click on the OK button straight after opening the listbox, to store the listbox value, it would delete all of the data already stored in the cell). This could lead to a lot of errors. What I would prefer is to populate the listbox checkboxes with checks according to the values that were stored in the cell upon opening the list box.
Is this possible?
If so do you have any tips on how to go about it?
Thanks
Flip
Debra,
Glad to see that you’re still answering questions. I’ve been using the code for a while, and it works very well – until I start inserting rows and rewriting named ranges. Then it reverts back to one selection only. I get the feeling that I’m missing resetting a range value somewhere in the worksheet. What can I test to make sure that the multiple event actions will work?
My code basically reads in a csv file, reformats it, checks for duplicates, adds new rows into the existing worksheet, copies the reformatted data to them, and then creates directories based on document numbers. However, after the insert process is done, the multiple list pulldown no longer works (but it does work as a single pulldown). Is there something obvious going on? Thanks.
Looking at it a bit closer, I’m never getting past the second Target count block of code:
If Target.Count > 1 Then GoTo exitHandler
‘ I put a msgbox – the Target.Count is equal to 1
On Error Resume Next
‘I have a message box here – and reach before the Set
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
‘ I have a message box here as well – and never reach this section of code – I’m breaking at the rngDV command.
Any suggestions?
hmmm….I ran across a comment that the SpecialCells function would work with only up to 8,192 non-contiguous cells – I’m definitely exceeding that. Could this be the problem? This was on the Microsoft site – it also states that no error message will be returned within VBA code – and that this is per design. thoughts?
Don, you could set the range to special cells in the Target cell instead:
I’ve been messing about with the various range values for a while – I can get intersections if I set them manually – e.g.,
Set MRange = Worksheets(“MyName”).Range(“DropDown1,DropDown2”)
If Not Intersect(Target, MRange) Is Nothing Then
….go through code
however – now I’m getting same very strange effects in the worksheet. For instance, my ActiveX calendar box is double applying dates, and I appear to be looping through the Worksheet_Change code multiple times – it seems that it’s not really taking the range. I’ve seen a related entry at that seems to indicate that there’s a bug in vba, and they recommend setting Application.Calculation = xlCalculationManual at the top of the worksheet and then Application.Calculation = xlCalculationAutomatic at the end. Frankly, I’m confused with the whole thing – it seems strange that I have code executing (the body within the range test writes a date to the end of my spreadsheet) when the condition isn’t met.
ah – the link is: http://www.excelforum.com/excel-programming/570066-problem-with-data-validation-dropdown-list-worksheet_change-event.html
Well…I’ve got it working – but I had to use the method above – once each time for each validation list. For whatever reason, I couldn’t group them into a combined non-contiguous range. For general changes (i.e., changes not requiring multiple clicks), I was able to use a grouped non-contiguous range. For multiple changes, I had to both include the Application.Calculation = xLCalculationManual and Automatic at the beginning and end of actions, and do individual range testing for each named range. Very odd. VBA has confounded me with its buggieness – particularly with built-in functions that simply don’t function properly (in a previous life I did C, Perl, GNU, SED/AWK, Unix Scripting, Fortran, Cobol, etc., programming – at least in those languages you knew why you had memory leaks and stack dumps). I’ve given up on the SpecialCells function entirely – it simply didn’t execute consistently enough – and I have to hand off this code to some non-programmers who aren’t going to tolerate occasional code malfunction. At any rate – I would not have known where to start without your code – so thank you very much for getting me off the ground floor.
Don, glad you got it working, and the SpecialCells limit seems to be fixed in Excel 2010.
Hi Debra
Your code for multiple selections from a drop down list into the same cell has been a life saver!!! I am having one small problem though…..
If I protect this spreadsheet so that others can’t edit formulas – you can no longer select multiple options in the samecell it overrides instead – any ideas on how to fix this?
Hi Debra,
I am sorry, but I am a newbie with this. Everything you offered above is clear but when I downloaded the sample data validation file into Excel 2010 and go to each tab to try the different options, the cell I select changes, i.e. 1.2.3… but it does not do what the code says, i.e. put in next cell, same cell with comma, …..
Is there something else I need to do to see each of the samples work?
thank you for your help.
Bill
BillJ, make sure that you enable macros when the file opens, and if you don’t see a security warning, you might need to change your security settings.
There are instructions here: http://blog.contextures.com/archives/2010/05/28/bypass-the-excel-macro-security-warning/
Hi Debra,
Thank you for your help. I did not have macros enabled and when I did I was able to get it to work.
I appreciate your time and expertise.
Best regards,
Bill
I have a similar issue that others have posted, but I am not seeing an answer that fits what I am runnign into. I have worsheets with the VBA coding for Multiple Selections in a dropdown list. It works great. I have selected these cells to be “unlocked” when I protect my sheet. Once I protect my sheet, the ddropdown option still works, but I can no loner select multiple items…I can only select one per cell.
Any suggestions?
Good morning, Debra:
Thank you for the great code!
I have been playing around with your “SeparateRows” code.
Just a few questions:
1)I would like to insert the validation menu at cell M2 only. Also, I would like to have the items appears from M3, M4, M5 and onward. How should I achieve this?
2)In addition, how can I set a limit to the number of rows that the code will populate? For example, I only want users to be able to populate M3, M4, M5, M6, and M7. If the user wants to populate past M7, an error message will pop up.
Thank you for your help in advance.
Sincerely,
David
I had trouble with the locking of the sheet as well.
But here’s something that’s worked for me:
I’ve simply added the Me.Unprotect to both the top and bottom of the code.
I can lock and unlock my sheet and it works fine. I do not have a password of my sheet however.
Hope it helps some.
Triplets,
The Me.Unprotect without a password works, but it promts for a password and you have to click okay without putting a password in. That would be fine for me, but I am sharing this spreadsheet with over 100 employees. It is not “fluid” enough to use that option. Do you know of other options that would allow part of the spreadsheet to be protected and the part where the I have my VBA code for my multi-item dropdown lists to be unprotected?
Any suggestions are apprecaited.
@Juli E – what version of Excel are you using? If you protect the sheet, with no password, you should be able to unprotect it without a password prompt appearing.
Also, if you unlock the data entry cells, before protecting the sheet, users should be able to select multiple items in those cells, even if the sheet is protected.
Debra,
I am using Miscrosoft Office Professional Plus 2010. I shut Excel down and started over. It appears to be working now on my end. I am going to send to other employees to test as well. Will it work the same if they do not have the same version as I do? Thanks, Debra!
Hi Debra. Thanks for the great code for the drop down lists. I think they are fantastic. I was wondering if you could help me modify the SameCellAddRemove code. This works exactly as I’d like except that it separates the items with a comma. I’d like the items to be separated with a line break. I looked at the coding and I’m not able to figure it out. My programming skills are limited to say the least. Any assistance you provide, would greatly be appreciated.
Matt
Hi Debra
Thank you so very much for this code. It makes laytechs like me look good! I found the tutorial which worked then realised I needed to apply it to different columns in a worksheet, so found these comments. Well done. Is it possible to apply different code for other columns in the same worksheet, eg SameCell and SeparateColumns?
Thank you so much for posting the instructions. Plus, having the code viewable. And the video (I’m a visual learner.) I work at a place where we cannot download anything from the ‘net. I typed the code in exactly as shown; changed the column number. Did the Data Validation. It worked perfectly. They think I am awesome. LOL! Thank you, thank you.
Hi Debra, your website is great. I’ve copied your “SameCellAddRemove” code. I’ve read above on how to limit or expand the code from one column to all columns. How do i go about limit to three specific columns, for example, columns 7,14 & 21? For example: If Target.Column = 3 Then
How do you change it to include 7,14 & 21?
@Michael, instead of If…End If you can use Select Case…End Select
Thanks Debra, apologies, I’m still having an issue. This is the code for “SameCellAddRemove” Where do I insert your solution above? Sorry!
@Michael, here is the last section of the code with Select Case, instead of If
It works! Thank you!! You are amazing. Have a good day
Hi, I have used the same code to select more than one items in same cell but it not working. I am using Excel 2007 even though I enabled the macros to run but still it not working.
@Raheel, are you having the problem with my sample file, or did you copy the code to one of your own workbooks, and it’s not working there?
I’m using your samecell multiple selections VBA code and I’m trying to apply it to drop down lists throughout a spreadsheet. I’ve been able to use the CASE statements to get most working but it occurred to me I have more drop downs selected than excluded. For example, I have many lists where multiple selections should be allowed but not drop downs like “male/female” or “yes/no”. Is there a way to let the code apply to the entire sheet and simply exclude the few lists you don’t want it to apply to?
The code is fantastic but I’m just trying to tweak it to accommodate my needs a little better. Thanks.
@Michael, you could put the columns that you want to exclude in one Case statement, and the multi-selection code in the Case Else section. For example,
Select Case Target.Column
Case 3, 4, 6
‘do nothing
Case Else
‘multi-selection code goes here
End Select
Debra it working now. Thank you very much for your support.
@Raheel, great, thanks for letting me know that it’s working.
I haven’t tried your fix yet but I will. It certainly looks like it will help us out. In the meantime, I’ve stumbled across another issue. While trying to paste values between two merged cells I got the typical error about merged cells. That’s one problem (any ideas). My fix was to go ahead and unmerge all my cells (my brute force and Infantry method) which has resulted in my columns being narrower than I had hoped. Now my drop downs are so narrow you can barely see the selections. I can’t increase the column widths due to screen width, print width, and display concerns. I’ve seen on this site a method of temporariy increasing pull down widths but I was hoping you could show me how to incorporate that method into the samecell multi selection code. I wasn’t sure if you could have two VBA sections applicable to the same pull downs.
Hello,
There is some great info on here. I want to make a same cell line break with the addremove feature as well. Could some one please provide me with the code, if it is possible. If not just the same cell line break would be enough. Thanks.
Hi Debra,
Many thanks for the videos and your tutorials as they are extremely helpful. I am trying to adapting the “same cell edit” for my project. I copied the code as is in my book as well as the box for “edit entries” and the admin notes but I end up getting an error message which I debug:
“Set rngEdit = Worksheets(“AdminNotes”).Range(“EditMode”)” appears highlighted.
Please advise and thanks again!
hb
@hb, make sure to name that EditMode cell as “EditMode” — there are naming instructions here:
http://www.contextures.com/xlNames01.html
Hi Debra,
Great information. Is it possible to alphabetize and/or sort the result order of a same cell (separated by a comma) or same cell line break?
For instance, the result “A, B, D, E, C” is out of order, can the result be sorted to alphebetize within the the same cell result?
@Greg, I don’t have an example that sorts within the cell, but you could find code that sorts within a cell, like this example from J.E. McGimpsey:
https://groups.google.com/d/msg/microsoft.public.excel.newusers/lWSPnkWHAAM/BNM0RsviaSAJ
Then, add that macro name to the worksheet_change event, so it runs after you update a data validation cell.
If your cells contain text, instead of numbers, change the data type from Double to String.
Debra,
Thank you for your quick response. I will get to testing. By the way, I really like the teaching methods and examples employed here. Thanks again.
~gh
Hi
This is really useful and we have transferred the same logic to an excel database. However, I need to lock the excel spreadsheet to prevent some cells being changed (obviously not those using the multiple value validation).
What I have found is that the spreadsheet then has to be unlocked to allow this VBA to work correctly on the cells using the validation. When the worksheet is locked it works the way it normally would, current string of values is replaced with new value. Existing values are not retained.
Is there any way around this?
Thanks in advance for any help.
Kevin
Hi i want to give hh:mm – hh:mm format in excel cells how do i do that, please do the needful
Debra, think it is great what you’ve done. I’m a total novice in programming. I want to combine two of your examples, samecell and linebreak. Can you advise how to do this?
Hi, Great function. I’m having a problem with getting the “Select Case Target.Address” portion. I’m using 2007, and need to only change the range as I’m using headings. The code with the column selected works fine, but when I added the change as suggested to Terry, the cell only displays the last selection instead of accumulating them.
My problem is this:
I have a form: Item name weight cost location
I have a list of items and in the next block I have their weight in the next their cost and the next their location.
I want to select the name in the drop down list under item name, but I want the weight box, cost box and location box to automatically fill with the data cell information. How do I do that?
Hi Debra,
I have been trying to create a drop down list that allows user to choose from more than one option. Still cant get your code to work on my spreadsheet. This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
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 Target.Column = AM Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
If you could plese help me.
Thanks
Not sure if you are still replying to this thread, but thank you so much for the time and effort into putting these together. I do have one question. Is it possible to modify the code to do two of these (SeparateRows & SameCellAddRemove) simultaneously for the same cell reference?
Hi Debra,
brilliant guide hopefully you will see this as I am at a loss.
I need to have multiple items in a cell (done) I then have a few columns after whose data is dependant on the previous (done)
How do I combine the two – when I try this my data validation in dependant columns only recognises my first choice. So if I pick Veg then fruit only veg options will show.
Thanks to anyone for help given.
Hi Debra, Just to start off you’re amazing. I’ve go a problem with images in comments and I was hoping for some help. I’ve been dabbling with creating a macro that allows a user to select an image from “my pictures” and then automatically inserting that image into a cell comment. Unfortunately, while the macro works great, we underestimated the popularity of the spreadsheet and now there are hundreds of pictures and the file has become too large. Is there a way to copy the image from the comment into a file structure and then replace the image in the comment with a link to the image’s new path. Not sure if this can be done but I need to do something to manage the size of the spreadhseet while still allowing images to be identified with the cell. By the way, your site is fantastic and has taught me a lot. Thanks!
Thanks Mike! Glad the info is helping you.
I don’t have an example that extracts pictures from comments, but there is sample code in this old newsgroup posting that might help you get started: Excel Comment Pictures
It’s for a single comment, and copies a picture of the comment.
You’d need to tweak it to go through all the comment cells, and remove the picture, then add a link as the comment text.
The link wouldn’t be clickable though, so it might be better to put the link in an adjacent cell.
Debra,
Is there a way to assign this code to specific cells rather then an entire column?
I have a macro that is assigned to a list at the top of column “C”, but need a macro similar to yours that will run in the cells/lists below it in column “C”.
Thanks,
John.
@John, you could change the code so it checks the target’s row and column:
If Target.Column > 3 And Target.Row > 2 Then
That is a great idea! Thank you SOOOO Much!
Regards,
John
I tried this and it is not working!
Hi Debra,
I apologise now if I’m duplicating this query, but I have a quick question about multiple selection with a comma separated and pivot tables? I used your coding to allow users to choose the tools they used to carry out research, which also allowed them to make multiple choices that would separate the items with a comma, and it works a treat.
The only problem is, when I try to add this data to a pivot table, it treats all items in the list as different variables. Although there are 6 items in the list, when it comes to counting how many times they used those tools, it won’t count how many times those items appeared in that column, but will consider each combination of multiple selections as one item.
Is there a way of over riding this so that it just lists the number of times each of those tools were selected?
Thanks.
Hi Sarah, unfortunately a pivot table won’t be able distinguish the unique items within a single cell.
Instead, you could use a COUNTIF formula to count the occurrences of each item in your data validation list.
I saw that two other people posted about using countif on the data validation multiple selection cells. I have tried using a countif, but that doesn’t seem to distinguish unique items within a single cell either. Is there a way to count occurrences of a within a cell? I have a spreadsheet in which a person can assign multiple people to a project, and there are line breaks when each new person is chosen from the list. My supervisor wants to be able to count how many times a person’s name is used in the spreadsheet. How to do this while keeping the multiple selections within one cell?
Hi Debra;
love these options, especially the ones that you can multi select and remove; hence for some reason it doesn’t work sometimes; i tried opening your excel form, and it doesn’t work, and reopen mine again, and it didn’t work – i don’t know what might be issue – because i have to submit it to my boss, and i don’t want her to see it’s not working…
Hi Debra,
Fantastic page; it’s exactly what I was after and easy to follow.
I am using the code from the ‘Separate Rows’ sheet on the downloadable file, and I was wandering how I might have the selections appear a few rows bellow the drop down list as opposed to next to it?
Hi Debra,
This is wonderful work, to select multiple items from a drop down list. I had no issue with creating it and using.
However, this code has created a change in the date format in the worksheet. I want to use the UK date format (dd/mm/yyyy), but when I apply in the background it works only in the US date format (mm/dd/yyyy). In the view it shows correctly (example June 9, 2013 will be appeared as 9/6/2013 but when convert to date serial it gives for September 6 2013. This issue is only for the 1-12 days.
How can we avoid date change.
Nalin
Code is absolutely brilliant. One question – is it at all possible to separate your choices with an ‘&’ or the word ‘and’ instead of a comma? The spreadsheet I’m working on will only have one or two choices from the list, so an ‘and’ would work perfectly.
Hello! I have used the code and had success, however, when I open my workbook after saving and closing it, the code no longer works. How can I prevent this?
Thank you in advance!
This code is great…and exactly what I need! However, when I add the code to existing code, I start getting errors. (Runtime Error 400 Form already displayed. Cant show modally) To clarify, if I remove the samecell code, the other subroutines run fine. I am only a beginning used of VB, so any help is appreciated.
‘ This checks to see if any hazard effects and severities are in the
‘ worksheet. If not, it warns.
Private Sub Worksheet_Activate()
Dim tRange As Range
Application.ScreenUpdating = False
Dim epty As Boolean
Worksheets(“DataSheet”).Visible = True
Set tRange = Worksheets(“datasheet”).Range(“A602:c700”)
epty = True
For Each c In tRange
If c.Value “” Then epty = False
Next c
Worksheets(“DataSheet”).Visible = False
Application.ScreenUpdating = True
If epty = True Then vbresult = MsgBox(“You do not have any hazard effects and severities loaded in this template. It is important that all people working on project risk documents have the same hazard effects and severities list loaded. See team QE to coordinate.”, vbOKOnly, “Info”)
End Sub
‘ This subroutine watches the activity on the sheet and determines what
‘ form to call depending on the cell that is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ Show the form to select standardized hazards.
If ActiveCell.Row > 8 And ActiveCell.Column = 2 Then
Application.ScreenUpdating = False
SelectHazard.Show
Application.ScreenUpdating = True
End If
‘ Show the form to select the appropriate effect/severity pair.
If ActiveCell.Row > 8 And (ActiveCell.Column = 6 Or ActiveCell.Column = 7) Then
Application.ScreenUpdating = False
Hazard_Effect.Show
Application.ScreenUpdating = True
End If
‘ Show the occurrence dialog and load the appropriate hint
If ActiveCell.Row > 8 And (ActiveCell.Column = 8 Or ActiveCell.Column = 11) Then
Dim s As String
Application.ScreenUpdating = False
Load Occurrence
Occurrence.Show
Application.ScreenUpdating = True
End If
‘ Show the risk dialog box for pre-mitigation.
If ActiveCell.Row > 8 And ActiveCell.Column = 9 Then
Application.ScreenUpdating = False
Load Risk
Call Risk.hintSet(Cells(ActiveCell.Row, 7).Value, Cells(ActiveCell.Row, 8).Value)
Risk.Show
Application.ScreenUpdating = True
End If
‘ Show the risk dialog box for post-mitigation.
If ActiveCell.Row > 8 And ActiveCell.Column = 12 Then
Application.ScreenUpdating = False
Load Risk
Call Risk.hintSet(Cells(ActiveCell.Row, 7).Value, Cells(ActiveCell.Row, 11).Value)
Risk.Show
Application.ScreenUpdating = True
End If
‘ Show the selection of risk controls.
If ActiveCell.Row > 8 And ActiveCell.Column = 10 Then
Application.ScreenUpdating = False
RiskControls.Show
Application.ScreenUpdating = True
End If
End Sub
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
‘ This subroutine selects items from a validation list
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
Dim strSep As String
strSep = Chr(10) ‘line break separator
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 = 13 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 16 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 _
& “, ” & strSep & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
This is amazing, thank you!!!!!
Can we add check box in the drop down menu so that we know we have either selected or not selected this option. what code should we add for that
Quick question. Is there a way to create a new row every time a new item is picked and then delete that row when the item is removed?
Put another way, if I have the values of A, B, C in a picklist in cell A1, if I pick A, it puts A in cell B2, if I go back and pick B, it puts B in cell B2. However, I already have information in Row 2. What I would like for it to do it to create a new row and then place B in that new row. However, If I delete B, then that new row is deleted. Thoughts?
Hi,
this is working fine except if i protected the sheet.
Please help.
Thanks
I’m trying to use the SameCellAddRemove code in a new workbook. I am creating multiple data validation fields. I’ve set up the lists in a ‘Lists’ tab. I have added the SameCellAddRemove code to my tab that I want to use my lists. I can get the drop downs, but not the multiple values (or have them removed). I’ve set up my lists file to use Column 1,3,5, etc. I’ve tried to modify the code to include the Select Case statement you provided in a previous post. Nothing is happening. I just get to select the drop down box.
Here is the code, I’m hoping you can help me!!!!
Option Explicit
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
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
Target.Value = newVal
Select Case Target.Column
Case 7, 14, 21
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
Case Else
‘do nothing
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
I downloaded the example spreadsheet and when I opened it in Excel 2010, I received the big red “Protected view” warning: Office has detected a problem with this file. Editing it may harm your computer.
This makes me distrust the file. Well, its also possible that I just sent my e-mail address to all the spammers in the world by posting this reply.
@bbouvier, the file has macros in it, to allow the multiple selections, but nothing unsafe or unusual. The file has been downloaded over 100,000 times, and I haven’t heard from anyone regarding problems, so I’m not sure why your system flagged it.
I wrote an article about this technique on the Microsoft Excel team’s blog, and you can download a version of the file without macros there. Perhaps that will work better for you:
http://blogs.office.com/b/microsoft-excel/archive/2012/08/21/select-multiple-names-from-a-drop-down-list.aspx
Thanks for the pointer to the Microsoft Excel team’s blog. Everything worked as designed. Now, I need to figure out how to make multiple values work in conjunction with dependent validation lists. For example, if column C is “State”, and column D is “County”, the ‘=indirect’ function would make the County validation list display only the counties in the State. However, the =indirect function fails if you select two states (e.g. California and Nevada) since there is no range named ‘California, Nevada’
Debra
If data validation is used, can the cell still be used for free text?
Lori, you can change the Error message setting in the data validation dialog box. There are instructions on my website:
http://www.contextures.com/xlDataVal04.html#Error
This was super useful. Thank you so much!
Hi, I copied pasted the code, but when I tried to select another item. It showed me an error “Label not define” then highlight exitHandler in the sentence below
If Target.Count > 1 Then GoTo exitHandler
I don’t know what should I do
Hi Debra,
This was extremely useful!! Thank you!
How do I filter the column by individual results?
For instance if column A has the following cells:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
I want to use the auto-filter option so that if I filter to show only “orange”, I will get A2 and A3. If I filter to show only “grape”, I will get A2 and A4.
Right now the auto filter shows the following options:
“apple, orange, grape”
“orange, apple”
“grape”
I would really appreciate it if you can solve this problem. Thank you so much!
Hi Debra,
Thank you for the code – very handy.
I made a small adjustment that keeps the macro from adding in the selection if it has already previously been selected, I hope you might find it useful:
Application.EnableEvents = False
commaChk = “,”
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal “” Then
If newVal “” Then
If InStr(1, oldVal, newVal) = 0 Then
Target.Value = oldVal & “, ” & newVal
End If
If InStr(1, oldVal, newVal) > 0 Then
Target.Value = oldVal
End If
If Len(oldVal) + 1 > Len(Target.Value) Then Target.Value = oldVal
End If
End If
I worked around your code, which worked nicely, to enable modification (add, remove) of items in the list.
I removed some lines. Works as long as items do not contains “, “.
Private Sub Worksheet_Activate()
Application.EnableEvents = True
End Sub
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
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 Not Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False
If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If newVal "" Then 'If new value isn't empty
If InStr(1, oldVal, newVal) > 0 Then 'If string is found in current value
If InStr(1, oldVal, newVal) = 1 Then 'If string is found at the beginning
If InStrRev(newVal, ", ") = 0 Then
Target.Value = oldVal 'Means user tried to add string that was already in current string
Else
Target.Value = newVal 'Means user deleted part of the string
End If
Else
Target.Value = oldVal 'Means user tried to add string that was already in current string
End If
Else
If oldVal = "" Then
Target.Value = newVal 'Means field was empty before update
Else
Target.Value = oldVal & ", " & newVal 'We append new value to the old string
End If
End If
Else
Target.Value = "" 'Means user cleared the field or didn't add item
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
I like the general abilities in this code but was wondering if the code to place the selected values in the next open cell could be changed so that I can remove a previously selected item from wherever it was placed. Similar to the idea of removing from the comma delimited string.
Thanks for your help.
Hello,
Not sure if this has been already asked, but I am trying to use a drop down list in Excel 2007 and I do not want it to remove items once they have been selected. For instance, for a transaction log, I want to have the user type in a description of a purchase, and in the column next to it choose from a list of categories this purchase falls into (such as food, or clothes). But then on the next transaction line, if the category item has already been selected once, I cannot select it again in the creel just below (say if there were multiple food or clothing purchases). Is there a way to allow the user to use the list item repeatedly from the drop down boxes in the same column?
never mind, excel 2010 fixed it
Debra,
I just wanted to take the time to thank you. This information was very useful.
Even reading the posts and replies from everyone was a great help as well.
After finishing a project for building a time management sheet for business travelers, I can hardly believe how well it works.
Trent
@Trent, you’re welcome! Thanks for letting me know that it helped with your project.
Hi,
Can u tell me; i want to show validation list matching word by typing some letter, then i select right one.
because if drop list so much then time is wast to scroll down…
Thank You
Dear Debra and other Forum members,
I read through the original post and some of the comments. What I could not determine is whether it is possible to use this multiple choice lists with other dependent data validations. For example:
In my sheet I have 4 dependent data validations in columns A-D. Selecting a value in A, limits the choices in Column B, Selecting the values in Column B, limits the choices available in Column C, selecting the values in column C, limits the choices in column D.
However, selecting a choice in Column D, should provide a multiple choice list in column E, as more than one value may be applicable to the item that was selected in Column D.
Is this possible?
Is it possible to do this in more than one place in the same worksheet?
Your assistance would be greatly appreciated.
Regards,
Louisa
I purchased the Data Validation Multi-select premium in an attempt to create multiple drop downs on protected sheets. I used the instructions for the sample and instructions for the dependent lists workbook. I already had a sheet set up with lists on another workbook that is open and 16 single select and 4 multi-select. I tried to use your vba, just to reference on column with a validation (sample had 5 and I changed to 9) and that did not appear to work at all. Further, you guide states on page 32: “Click on any of the buttons, or the listbox, and you can see the selected control’s properties in the Properties window.” I cannot see “properties”
Any help would be appreciated. I desperately need multiple drop downs on a protected sheet (the affected cells would be unlocked, of course).
Thank you..
@Ed, thanks and I’ve sent you an email.
Hi Debra,
I have been using the coding succesfully for a spreadsheet at work. I am currently trying to analyse the data that I have accumulated and trying to count up the individual items in each multiple choice category. However, the CountIf function doesn’t seem to be working in Excel 2010.
I wonder if there is a remedy for this, given that the function is only designed to work with single items in a dropdown?
Christopher
Hi Christopher,
You can use the “*” wildcard with COUNTIF to find text within a string. There’s a sample on my website:
http://www.contextures.com/xlFunctions04.html#String
Watch for partial words though — it would find “pen” within “pencil”, as well as separately
Hi Debra,
Thank you for all of your hard work, it has been extremly helpful! If you do not mind, I have a followup question regarding a problem I have encountered with the SUMPRODUCT function.
On a different sheet, I am collecting the totals for the available variables in the multiple selection dropdown. I have used the SUMPRODUCT function because I am only calculating the totals when they are considered a “new” case. The formula works fine, until there are multiple selections in a cell. SUMPRODUCT can no longer recognise the variable if there is more than one variable in a cell. Is there a way to fix this? It would be a great help, seeing as I am trying to calculate totals. I hope I was able to adequately convey the issue!
Thanks in advance!
This is a great piece of code and this forum has answered all my issues so far! However I am having the following issue that had been posted twice and went unanswered! I would greatly appreciate help! Thank you!
Alex
December 27, 2010 at 7:59 pm · Reply
Hello Debra, I know the Same Cell example was simply an example, but I liked it alot and I added it to a worksheet as you know. I get a little problem that maybe you have made a fix for it.
The code works great, but when I click on the drop-down list and for some reason click another cell in accident, it duplicates what ever is in the cell at the moment.
Example: cell A1 currently has
apples, pears, oranges in the cell
for some reason, when I activate the cell A1 with the text, and then drag my mouse away and click some other cell, it generates duplicate data of whats inside cell A1. So I end up with: apples, pears, oranges, apples, pears, oranges.
I un-checked ‘ignore blanks’ but it still happens.
Any suggestions?
Thank you,
Alex
I cant believe I mixed this part on the original post! what an oversight! FIXED IT!
Hi Sarah … can you share how the duplication in the same cell was fixed? I’m having the same issue and not having much luck finding a solution. Thanks in advance!
Debra,
Is it possible that you can select a max number of items in the cell?
Example. max 3 dropdown items items in each cel?
Thank you,
Henk
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
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 = 11 Or Target.Column = 21 Or Target.Column = 31 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
Henk, you could check the number of commas in the OldValue, and if it already has 2 commas (separating 3 items), then don’t allow another selection
Hi Debra,
Great information! Worked perfectly! I had another question that I see a few other readers had asked related to multiple selects but I did not see an answer. Maybe it is not possible to do.
For example, if column A has the following cells:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
Is there some way to set the filter options to each item (e.g. apple or orange or grape) and not the string of items (e.g. apple, orange, grape or orange, apple or grape)? So if I want to filter and show only “orange”, I will get A2 and A3. If I filter to show only “grape”, I will get A2 and A4.
Right now the auto filter shows the following options:
“apple, orange, grape”
“orange, apple”
“grape”
Is this possible?
Thank you!
Bonnie
@Bonnie, in Excel 2010 and later, if you start typing in the Filter’s Search box, the list will be automatically filtered to show only the rows that contain the text you typed.
Hi,
i need below formula how i can use this formula.
=RIGHT(F111
=Left(F111
Hello – I am wondering if there is any way to use the multi select VB code to enable the user to select multiple items from the drop down list using the CTRL key? I added this mutli select capability to a worksheet and my boss is disappointed that users can’t select multiple items at once using CTRL.
@Katheryn, your boss might prefer to use the Listbox version instead:
http://www.contextures.com/excel-data-validation-listbox.html
This has been very helpful, but I really need to just have the multiple selection separated by commas in just one cell, not a whole column. I used the code for “SameCellAddRemove” and it was working great until I realized it was affecting all the other cells in a column that can’t have the comma thing happening to it every time I type in them
This is what I currently have in my spreadsheet that I need to now refer to Columns 6 & 8 AND only Row 2!
Option Explicit
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
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
Target.Value = newVal
Select Case Target.Column
Case 6, 8
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
Case Else
‘do nothing
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
Just below this line:
add a line of code to check the row number:
This does not work for me at all. I have the code entered exactly, but can still only select a single item. Help! (I’m on a MAC)
@Brian, I don’t have a Mac, so can’t test on that system.
Do you have macros enabled, when you open the workbook?
Debra,
I’m Using SameCell VB and just wondered of I could have my data validation list on another worksheet to keep my lists clean and away from the main grid. Is that possible?
Hi Debra,
I was able to use your code to allow for the multiple selections and it’s working great except that I get the little green triangle trace error in my cell whenever a 2nd choice is selected. I can individually click to ignore error each time, but is there a way to get this from occurring?
Thank so much!
@Jon, you could turn off all the data checking for tables, if that won’t cause other problems for you. This setting will affect all workbooks that you open.
To change the setting, click the Error Message button when it appears, then click Error Checking Options
In the Error Checking Rules section, remove the check mark for “Data entered in a table is invalid”
Click OK
I am using the option to fill the entries down the column, but I want to use this multiple times on a sheet. When I make my selection on each row (same column) that I have data validation (example: rows 8, 16, 23, 30; all col. D have unique data validation selections), the selections appear in col. E in the same row as the data validation cell, and go down as expected. The problem is that if I go back up and change the entry in D8, the additional selection is put in a row after the last selection (ex: E31) after the last data validation box selection result. How can I make separate areas so that I can have multiple selection cells on a page. I am using this for a questionnaire and need the next question to ‘reset’ so to speak with a new set of responses but still allow the user to go back to the first one and add an item in the right area.
Thanks.
Hi Debra,
I am working on a pricing sheet for my work and I used this code to select multiple items from a drop-down list to be priced. In another cell I need to write a formula that will reference the cell with multiple selections and add the corresponding prices to those selected cells together in the same cell. I have the formula worked out however excel is not recognizing that the list is showing multiple cell selection. Is there an option in this code to allow for a cell to recognize that another cell is validating multiple selections?
Thanks,
Hi Jacob,
There’s nothing in the code that will help another cell the recognize multiple values. You could try to modify the code, so it puts the prices in another cell, when adding the items.
There is a sample file on my website that changes a product name to a product code, after it is selected. That might give you some ideas. It’s on the Sample Files page — look for DV0004 in the Data Validation section:
http://www.contextures.com/excelfiles.html
I created a drop down list that allows multiple selections from the list but once I make my selections, if I want to remove a selection(s) from the cell I get an error message an cannot remove it. The only way I can remove selections I do not want in the cell is to clear the cell completely and re-select. Do you have a solution that will allow just clearing particular selection(s) instead of the entire cell. Here is the code I used to allow multiple selections:.
Private Sub Worksheet_Change(ByVal Target As Range)
‘Updated: 2016/4/12
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 “” Then
If xValue2 “” Then
If xValue1 = xValue2 Or _
InStr(1, xValue1, “, ” & xValue2) Or _
InStr(1, xValue1, xValue2 & “,”) Then
Target.Value = xValue1
Else
Target.Value = xValue1 & “, ” & xValue2
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
Hi Debra!
i have a question, how can i make a drop down list which brings a row of data (6 or 10 columns or may be more), like the example of the fruits and vegetables…
Is it possible to create a drop down list in a cell based on specific selections from other cells?
For example, if we have some values on column A and B, than in column C to have a specific drop down list with 4 values; and if in column A and B we have other values, in column C to have a different drop down list with only 2 elements, e.g.
Many thanks in advance!
How can I change the script to allow this to work on every cell in the column. For example I have the script working for cell I8 how do I make it work for cells I9 onwards?