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. 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.
Fill Across a Row
For example, each item selected in this drop down list fills the next empty cell at the right. You could use this technique to fill player names across a list of innings, or employee names for days of the week.
Fill Down a Column
In some worksheets, you might prefer to fill down, instead of across.
Add Values To the Same Cell
Instead of filling other cells, you can keep all the selections in the same cell, separated by commas.
Use Programming to Add Multiple Values
The code to allow multiple selections runs automatically when you make a change on the worksheet. To see the code for the SameCell sheet, you can right-click the sheet tab, and click on View Code.
In the code, column 3 is the only one where the change will occur. In your workbook, you could change the 3 to a different number. Or, if you don't want to limit the code to a specific column, you could delete the two rows that are marked with a red circle.
Download the Sample Data Validation File
To experiment with this technique, you can download the zipped sample file from the Contextures website: Select Multiple Items from Excel Data Validation List
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.
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
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.
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:
If Target.Value = "" Then GoTo exitHandler If Target.Offset(1, 0).Value = "" Then Target.Offset(1, 0).Value = Target.Value Else Target.Offset(1, 0).Value = _ Target.Offset(1, 0).Value _ & Chr(10) & Target.Value End IfMichael, 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.
:)
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.:
Select Case Target.Address Case "$A$7", "$C$5" 'code here End Select[...] 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:
ws.Protect Password:="myPwd", _ DrawingObjects:=False, _ Contents:=True, _ AllowInsertingColumns:=True, _ AllowInsertingRows:=TrueMany 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:
If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Select Case Target.Column Case 3, 4, 5 Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End Select End IfHi 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:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim iCol As Integer Dim iStopA As Long Dim iStopB As Long iStopA = 19 iStopB = 60 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 If Target.Value = "" Then GoTo exitHandler If Target.Validation.Value = True Then Select Case Target.Column Case 10 iCol = Cells(Target.Row, iStopA).End(xlToLeft).Column + 1 Case 43 iCol = Cells(Target.Row, iStopB).End(xlToLeft).Column + 1 Case Else GoTo exitHandler End Select Cells(Target.Row, iCol).Value = Target.Value Else MsgBox "Invalid entry" Target.Activate GoTo exitHandler End If End If exitHandler: Application.EnableEvents = True End SubFirstly: 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.
@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.
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
@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:
Option Explicit
' Developed by Contextures Inc.
' http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Cells(Target.Row, 2) = Date
End If
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
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
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"
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.