Select Multiple Items from Excel Data Validation List

Select Multiple Items from Excel Data Validation List

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.

Data Validation Drop Down

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.

_________________

336 thoughts on “Select Multiple Items from Excel Data Validation List”

  1. 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.

    1. 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.

  2. 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.

  3. 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.

  4. 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

  5. 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

  6. 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

  7. 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’

  8. 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

  9. 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?

  10. 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

  11. 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

  12. 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

  13. 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.

    1. 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.

  14. 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!

  15. 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!

  16. 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.

  17. 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.

  18. 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

  19. 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

  20. 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!

  21. 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 If
    
  22. 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

  23. 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,

    If Target.Column = 6 or Target.Column = 8 
  24. 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.

  25. 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.

  26. 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

  27. @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)

  28. 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.

  29. 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.

  30. 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

  31. @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.

  32. 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.

    1. 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

  33. 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.

  34. 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!

  35. 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?

  36. 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

  37. 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!

  38. 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

  39. Dear Debra,

    The code is working perfectly. However, Is it possible to avoid to select twice the same items from the dropdown list ?

  40. 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

  41. 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.

  42. 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!

  43. 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!”

  44. 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

  45. 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?

  46. 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

  47. 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!

  48. @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!

  49. 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?

  50. 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

  51. Hi Debra and everyone,
    One more question. What if i want to do same cell line break? How do i code that?

    Thanks,
    Elaine

  52. 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.

  53. 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

    1. 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!!

      1. 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

  54. […] where you need several cells with multiple options, you could use the technique for selecting multiple items from a data validation drop […]

  55. 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

  56. 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

  57. 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!

  58. […] 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. […]

  59. 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

  60. 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

  61. 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

  62. 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.

  63. 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

  64. For some reason, the code isn’t working for me.
    I’m using excel 2007.
    Macros are enabled.
    Any suggestions?

  65. 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

  66. 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…)

  67. 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

  68. 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

  69. 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

  70. 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

  71. 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.

  72. This only works if you don;t have password protection on the sheet. If you do it asks for the password.

  73. 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”

  74. 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

  75. 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:=True
    
  76. 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

  77. 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

  78. 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 ???????

  79. 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

  80. 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.

  81. 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.

  82. 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.

  83. 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

  84. @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

  85. 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

  86. @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

  87. 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

  88. “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.

  89. 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. 🙁

  90. 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

  91. 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.

  92. 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.

  93. 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

  94. @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”

  95. 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

  96. 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!

  97. 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.

  98. 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

  99. 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

  100. 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!

  101. 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

  102. 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

  103. 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

  104. 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?

  105. @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 If
    
    1. 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 🙁

    2. 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?

  106. 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

  107. 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

  108. Thanks Debra,
    That worked great for #2
    If you ever find a way to fix the other questions that would be fantastic.

  109. 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!!!!

  110. 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?

  111. 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

  112. 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

  113. 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!

  114. 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.

  115. 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”

  116. @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

  117. 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.

  118. 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

  119. 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.

  120. 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?

  121. @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

  122. 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.

  123. 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?

  124. 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

  125. 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

  126. @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 Sub
    
  127. 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?

  128. 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.

  129. 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

  130. @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.

  131. 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?

  132. 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 !

  133. 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.

  134. 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,

  135. […] 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 […]

  136. 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

  137. 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!

  138. 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

  139. 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

  140. 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

  141. 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

  142. 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.

  143. 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.

  144. 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.

  145. 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.

  146. 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!

  147. 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.
    ' 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!

  148. 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?

  149. 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

    1. 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

  150. 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

  151. 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.

  152. 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

  153. 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

  154. 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.

  155. 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.

    1. @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”

      1. 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?

  156. 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!~

  157. 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.

  158. 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.

  159. 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

  160. 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

  161. 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

  162. 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.

  163. 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?

  164. 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?

  165. 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.

  166. 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.

  167. 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?

  168. 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

  169. 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

  170. 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?

  171. 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

  172. 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.

    Option Explicit
    ' Developed by Contextures Inc.
    ' www.contextures.com
    Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Unprotect
    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
      Select Case Target.Column
        Case 26, 92, 93
            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 If
    exitHandler:
      Application.EnableEvents = True
    Me.Protect
    End Sub
  173. 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.

    1. @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.

      1. 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!

  174. 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

  175. 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?

  176. 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.

  177. 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?

    1. @Michael, instead of If…End If you can use Select Case…End Select

      Select Case Target.Column
        Case 7, 14, 21
          If oldVal = "" Then
            'do nothing
            Else
            If newVal = "" Then
            'do nothing
            Else
            Target.Value = oldVal _
              & ", " & newVal
            End If
          End If
        Case Else
          'do nothing
      End Select
  178. Thanks Debra, apologies, I’m still having an issue. This is the code for “SameCellAddRemove” Where do I insert your solution above? Sorry!

    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 = 3 Then
        If oldVal = "" Then
          'do nothing
          Else
          If newVal = "" Then
          'do nothing
          Else
            lUsed = InStr(1, oldVal, newVal)
            If lUsed > 0 Then
                If Right(oldVal, Len(newVal)) = newVal Then
                    Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
                Else
                    Target.Value = Replace(oldVal, newVal & ", ", "")
                End If
            Else
                Target.Value = oldVal _
                  & ", " & newVal
            End If
          End If
        End If
      End If
    End If
    exitHandler:
      Application.EnableEvents = True
    End Sub
    1. @Michael, here is the last section of the code with Select Case, instead of If

        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
      
  179. 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.

  180. 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.

    1. @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

  181. 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.

  182. 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.

  183. 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

  184. 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?

  185. 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

  186. 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

  187. 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?

  188. 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.

  189. 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?

  190. 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

  191. 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?

  192. 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.

  193. 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!

    1. 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.

  194. 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.

  195. 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.

      1. 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?

  196. 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…

  197. 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?

  198. 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

  199. 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.

  200. 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!

  201. 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

  202. 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

  203. 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?

  204. 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

  205. 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.

    1. @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

  206. 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’

  207. 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

  208. 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!

  209. 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

  210. 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

  211. 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.

  212. 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?

  213. 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

  214. 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

  215. 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

  216. 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..

  217. 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

  218. 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!

  219. 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

      1. 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!

  220. 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

  221. 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

    1. @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.

  222. 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.

  223. 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

    1. 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

  224. 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)

  225. 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?

  226. 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!

    1. @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

  227. 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.

  228. 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,

    1. 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

  229. 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

  230. 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…

  231. 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!

  232. 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?

Leave a Reply to Hannah Carleton Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.