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. 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 for the sample file, near the end of this article.

More Articles on Select Multiple Items

[Update]: Since posting this article on selecting multiple items from an Excel drop down list, I've added more articles on this topic. Here are a few links to updates:

You can find the full list on the main page for this technique:

Multiple Selection Options

In the sample file, there are different examples of the code, with options for where the selected items are placed. You might want all the items in a single cell, or in adjacent rows or columns.

Fill Across a Row

For example, each item selected in this drop down list fills the next empty cell at the right. You could use this technique to fill player names across a list of innings, or employee names for days of the week.

Data Validation Fill Across

Fill Down a Column

In some worksheets, you might prefer to fill down, instead of across.

Data Validation Fill Down

Add Values To the Same Cell

Instead of filling other cells, you can keep all the selections in the same cell, separated by commas.

Data Validation Fill Same Cell

Use Programming to Add Multiple Values

The code to allow multiple selections runs automatically when you make a change on the worksheet. To see the code for the SameCell sheet, in the sample file, you can right-click the sheet tab, and click on View Code.

Sheet Tab View Code

In the code shown below, column 3 is the only one where the change will occur. In your workbook, you could change the 3 to a different number. Or, if you don't want to limit the code to a specific column, you could delete the two rows that are marked with a red circle.

Data Validation Multiple=

Download the Sample Data Validation File

To experiment with this technique, you can download the zipped sample file from the Contextures website, on the Excel Data Validation - Select Multiple Items page.

NOTE: In some of the code, you might have to change the range names or the sheet names, if you copy the code to a different workbook. Some code examples refer to a specific row or column number, and you might also need to adjust those.

In the sample file, a macro runs automatically when the workbook opens, to protect the "SameCell" worksheet. If you do not need that code, you can remove it from the ThisWorkbook module. If you remove or rename the SameCell sheet, without deleting the Workbook_Open code, you will see an error message when the file opens.

Buy the Premium Version

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. Instead of selecting from the drop down, a list box appears when you click the cell.

It automatically selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.

The premium version works with dependent lists too, and runs on a protected worksheet. There is also an option of showing a multi-select listbox, or a single-select version (this is helpful when working with dependent lists -- you don't want multiple items selected in the main columns).

The kit has 3 sample files, and a user guide, with details and screen shots, on how to add this technique to your own workbooks. Click here for details: Data Validation Multi Select Premium.

Watch the Data Validation Video

To see these techniques, and a few other multiple selection examples, you can watch this short video.


_________________

307 comments to Select Multiple Items from Excel Data Validation List

  • ingeno79

    Excellent. This is just what I needed. I actually extended this a bit so that the default entry on the list always says "pick from list".

    Next thing to add would be a feature that goes through the already created list of values from the drop-down list and exclude those values on the drop-down that have already been added.

  • Thanks ingeno79, adding a default entry is a good idea.
    To exclude the items that are already added, you could combine this with the technique in this tutorial:
    Hide Previously Used Items in Dropdown

  • SP

    This works great, but I have moved the script into a new workbook and each time I open this new workbook and try to make a change in the dropdown list a warning comes up to say that the cell or chart is protected. I can unprotect the sheet by going to tools and protection, but each time I open it i get the same error message. I need to remove this automatic protection which seems to come into effect on open so that I can have other users use my sheet without getting warnings.

    Your help would be much appreciated.

  • SP, there's code in the ThisWorkbook module, that runs when the workbook opens. Perhaps you copied that to your new workbook too. If so, just delete that, and the SameCell sheet won't be protected automatically.

  • SP

    Thanks Debra – works great now.

  • Steve

    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

  • Kai

    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

  • Rafael

    marvelous, didn't know it was possible.
    it is exactly what I was in need.

    thx,
    Rafael

  • Juanita

    Works great, but..... One question. If you select an item in err, what is the best way to remove it? I went to the formula bar and deleted the duplicate item but it gives me an err " The Value you enter is not Valid" "A user has restricted values that can be entered into this cell"

    Also, when I select more than two items I receive the err " The value in this cell is invalid or missing, ...."

    Thanks for your help,
    Juanita

  • Juanita, if you want users to be able to edit the cell, you can change one of the Data Validation settings. On the Error Alert tab, in the Data Validation dialog box, remove the check mark for 'Show error alert after invalid data is entered'

  • cameron

    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

  • Mary

    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?

  • Juanita

    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

  • cameron

    I am really a novice programmer and I can't seem to get this to work for me. Can you please explain how to modify the code to apply the comma separators in the same cell to the entire sheet?

    Any help is appreciated!
    Thanks!
    Cameron

  • Cameron, right-click on the CommaSeparated worksheet tab and delete the lines with the red dots. Then the code should apply to the entire sheet.

  • cameron

    I am trying to use the code from the "Same Cell" tab. I am deleting the lines with the red dots and I can not get the code to run.

    I am obviously doing something wrong, but I do not know what it is.

    I am inserting a module in VB, pasting the code, deleting the lines with red dots (without re-formatting the code. Do I need to be deleting the blank lines where the code used to be and evening up the tabbing of the "End If"s) and then hitting run, naming it "comma" and trying it out in the sheet.

    Can you point out if this is the wrong way to go about this?

    Thanks!
    Cameron

  • Cameron, the code should be pasted onto the module for the worksheet where you want to use the code.
    So, if you're trying to use this on a worksheet named "MyDataEntry", right-click on that sheet's tab, click View Code, and paste the code there.

    • Md. Abdur Razzak

      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.

  • cameron

    hallelujiah!!!
    it works!

    thanks you, debra, so much!

  • Cameron, you're welcome! Glad you got it working.

  • John

    I have downloaded the zipped file but when I go to try the different tabs they are not working. When I copied the SameCell coding to my spreadsheet it worked great but I wanted to try some of the other methods and now none of them work. Help!

  • John, make sure to enable the macros when you open the sample file, and then the different tabs should work.

  • John

    Thanks for your response. I must have glitched something because after I closed all my open Excel files and re-opened Excel and everything worked fine.

    I need your help on some of the programming. I want to limit the programming to a specific column because in other columns I want to use a regular pull down menu. This is the first time I have ever enter any type of programming in Excel! What additional programming language would I need to use to limit the CommaSeparated, LineBreak and SameCell programming to a specific column (Column 10 in my spreadsheet)?

    Thanks!

  • John, most of the examples refer to a column number, e.g.

    If Target.Column = 3 Then

    Change that column number to 10, and it should work the way you want.

  • Grace

    Hi Debra, Your script on this multiple selection on drop down list is very useful. For the "SameCell" sample, one issue I encountered is when you selected a wrong entry and tried to delete it, entry can not be deleted but instead the cell will contain "Old value" + "new value".

    Sample:
    Old Value = "One, Three" {then I want to delete "Three" by selecting "Three" and press Delete , then press Enter}
    New Value = One, Three, One (which is the Old + New)

    Hope you can help me with this bug. Thank you.

  • Grace, unfortunately, there's no way to distinguish your correction from a selection in the drop down list. When you change the cell contents to "One", that becomes the new value, and it's added to the old value.
    You could clear the cell, then select One from the drop down list.

  • Michelle

    Hi, I am able to apply this code to my worksheet just fine. However, when I select an item in error and I try to delete it from the cell, it actually adds another "wrong" item. The only thing I can think of is to clear the entire cell and start over with my selections. Is there any other way to work around this?

    Thanks in advance

  • Hi Michelle, you can clear the cell and start over, or stop the event code from running while you make the change.

  • weikee

    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

  • Michael

    Hi Debra,

    This is almost exactly what I have been attempting to accomplish. However, I need this to run on multiple columns, but not the entire workbook. Is there a simple way to do this?

    For example; I want it to apply to all rows greater than 2 and all columns higher than C...

    Thanks for the help!

  • weikee, you can change the code for the line break example, so the text goes into the cell below:

        If Target.Value = "" Then GoTo exitHandler
        If Target.Offset(1, 0).Value = "" Then
          Target.Offset(1, 0).Value = Target.Value
        Else
          Target.Offset(1, 0).Value = _
            Target.Offset(1, 0).Value _
            & Chr(10) & Target.Value
        End If
    
  • Michael, you can change the code so it checks the target's row and column:

       If Target.Column > 3 And Target.Row > 2 Then
    
  • weikee

    Hi Debra, It works perfect! Thank you so much again for sharing your expertise.

    weikee

  • boeh

    How can I make this work for Excel 2007?

  • Tinks57

    Hi, this is fantastic. I am very novice and have a small problem. I used adding to the same cell macro and changed column to column 6. I also want to run the same macro but from column 8 in the same spreadsheet, but I get an error message. i have tried simply calling it a different name but obviously i am doing something wrong. I would appreciate any help you can give me. Keep up the good work, this is fab. Thanks, Anna

  • boeh, the macro should work in Excel 2007. What problem are you having? Did you enable macros?

    Tinks57, in the code, you can change the line that mentions the column, to include both columns. For example,

    If Target.Column = 6 or Target.Column = 8 
  • Tom

    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.

  • Frank S.

    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.

  • Peter

    Hi Debra,

    This works perfectly until I save & reopen it. When I reopen it reverts to one selection only per cell. I have copied the code from the same cell tab, replaced it with the appropriate column no & saved as macro-enabled. Any Ideas?

    Thanks,

    Peter

  • @Peter, make sure to enable macros when you open the file -- there's a security warning bar just above the formula bar.
    If you don't see the security warning bar, change your macro settings to "disable all macros WITH notification". (Office button, Excel Options, Trust Center, Trust Center Settings)

  • Nik Vyas

    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.

  • Larry A

    I'm jump in in the middle of this thread.

    Kudos for your Same Cell and Hide Previous routines. I have implemented the Same Cell (using comma separation) successfully, but my Hide Previous routine needs to work differently than the way you've implemented yours. I want to implement the Hide Previous the next time I click the drop-down in the SAME CELL and remove all previous choices used in THAT CELL from the available choices. This makes for a more effective pick list becuase there'd never be a need to pick the same choice more than once on a given day (each row).

    When I get to a new row, my full list of available choices starts all over again.

    COMMENT: I am not a VB programmer but I assume that there is not a way to open up a combo box of multiple choices and either control-click on any of them or click on checkboxes associated with each. That would get around all these INDEX, OFFSET and MATCH functions.

    But cheers to you for figuring out how to do it and thanks for your many useful contributions.

  • Hi Nik,
    Use a named range as the data validation list source, as described here:
    http://www.contextures.com/xlDataVal01.html

  • Nik Vyas

    That's fantastic Debra.

    Thanks so much for your help.

    Nik

  • Rachel Richardson

    Hello. Thankyou for all your comments and videos i have managed to get things really rolling :)
    My question please, is it possible to pick any number of names (for eg)in a drop down list without having to drop the list down again each time. Like a tick box against each item in the drop list. cheers

  • @Nik Vyas, you're welcome, glad it helped you.

    @Rachel Richardson, you can only select one item at a time with a combo box, but you could use a list box instead, and set its properties to allow MultiSelect.

  • Rachel Richardson

    Hello Debra. Have tried that but having difficulties, what i am after is selecting say 3 things from a list (check box ones wanted) and then the chosen options stay in that same cell spaced by a comma. Is this possible please.

  • Rachel, I don't have an example of using a list box to enter multiple items in a cell, but I'll try to get one posted in the next couple of weeks.

    • Sanjay Gulati Musafir

      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

  • Mayur

    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.

  • Mutrata

    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!

  • Sam

    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?

  • LB

    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

  • Laura

    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!

  • Adam

    This script dosn't work for office 2003 ? Any suggestions ? Thanks.

  • Adam, the code was created in Excel 2003, so should work in that version. Did you enable macros when you opened the workbook?

  • Myrna

    Hi Debra,

    Great work!! Can you add 'All' in the selection list and when selected it will add all the values from the list? I have a use for this in a chart.

    Cheers,
    Myrna

  • Myrna, thanks! I don't have an example that lets you select All, but it's an interesting idea. If I put something together I'll post about it on the blog.

  • Junior

    Dear Debra,

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

  • Fiona

    Dear Debra,
    I have been looking for this in the past few days and is realy work for ppl like me who has no any programming background. Thanks thanks thanks!
    It work perfect for me and I am able to apply to more than one columns and not entire sheet.

    Cheers,
    Fiona

  • Junior, I don't have an example that prevents selecting the same items twice, but maybe someday!

    Fiona, You're welcome! Thanks for letting me know that the example helped, and you got it working perfectly.

  • Nkiruka

    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!

  • Lisa

    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!"

  • spencer

    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

  • beth

    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?

  • Trevor

    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

  • Lisa

    Debra,

    No need to answer my question on October 25th – using the "Separate Rows" example. I figured out how to move the selected items directly underneath the data validation dropdowns (next available row), rather than the column to the right.

    Thank you for the examples!

  • @Lisa, thanks for letting me know that you figured it out.

    @Spencer, can you give an example or a bit more detail on what you're trying to check?

    @beth, it should work without problems in Excel 2007. Is there other code running in your file?

    @Trevor, there's nothing in this sample file that will stop duplicate selections. Something to consider for future version though -- thanks for asking!

  • AN

    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?

  • LB

    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

  • Elaine

    Debra,
    Thanks!
    What if i want to do data validation and run the same cell code for columns 6, 9, 12?

    Troubled,
    ELaine

  • Elaine

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

    Thanks,
    Elaine

  • Terry

    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.

  • santosh

    I have created the file with datavalidation drop down list but while e-mailing the same the same is disappearing (code) from the file. The receipient also want to see the changes? Can someone help on this

  • @Terry, you could use Select Case and list the cell addresses, e.g.:

    Select Case Target.Address
         Case "$A$7", "$C$5"
            'code here
      End Select
    • Dora

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

  • Contextures Blog » Add List Box to Excel Worksheet

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

  • Alex

    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

  • LB

    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

  • Alex

    Wow! Thank you so much LB! It works great and thank you Debra for the ease you bring to us users to become better at Excel!

  • @LB, Thanks! Glad you were able to help Alex out with his code.

    @Alex, you're welcome!

  • Contextures Blog » Excel Data Validation Update

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

  • Alex

    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

  • Andrew

    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

  • Melanie

    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

  • Viktor

    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.

  • Jody

    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

  • berniceerene

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

  • berniceerene

    Nevermind. Once I closed and reopened, it worked perfectly. Thanks!!

  • berniceerene

    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

  • Thierry

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

  • Thom

    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

  • LB

    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

  • Matt

    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

  • Lyle

    Hi Debra , I have tried to delete error entries in the same cell dropdown list with comma separator but I get more entries from the dropdown list instead. Would you be able to advise me on this. Thank you very much

  • Lyle, the SameCellEdit sheet is the only one with code that uses the EditMode cell.

    You could revise the code on the SameCell sheet, to also check the EditMode cell's value.

  • Angela Felton

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

  • Angela, you can add a line of code at the top of the procedure, to unprotect the worksheet, e.g.:
    ActiveSheet.Unprotect Password:="abc"

    Then, in the exitHandler section, reprotect the sheet:
    ActiveSheet.Protect Password:="abc"

  • Angela Felton

    Thanks for replying Deborah. The password problem is now solved, but has created another issue. When protection is re-applied,it comes back on with only minimum default permissions to select locked and unlocked cells. I need users to be able to adjust row and column sizes, insert hyperlinks and edit objects. Any ideas welcome

  • Angela, record a macro while you protect the worksheet, with all the settings that you want.
    Then, add the password to the recorded settings, and put that in the multiple selection drop down macro.

    For example:

    ws.Protect Password:="myPwd", _
        DrawingObjects:=False, _
        Contents:=True, _
        AllowInsertingColumns:=True, _
        AllowInsertingRows:=True
    
  • Angela Felton

    Many thanks Deborah, that works a treat!

  • Angela Felton

    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

  • Angela Felton

    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

  • Angela Felton

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

  • Jen

    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

  • Angela Felton

    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.

  • Tom

    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.

  • Phil Culver

    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.

  • Mark

    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

  • LB

    @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

  • Phil Culver

    LB in GA, Thank you!

  • Mark

    Hey LB,
    I would be happy if you or another person in this "thread" would like to help me instead of Debra.
    Thanks to all of you,
    Mark in SC

  • @Mark, at the end of the code, you could clear the contents of the data validation cell, by adding this line of code:

    Cells(lRow, lCol + 1).Value = Target.Value
    Target.ClearContents '< =====THIS IS THE NEW LINE
    End Select

  • @Mark -- you can try the line of code that I just posted in your original question

  • Mark

    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

  • george

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

  • Rick

    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. :(

  • Mark

    Debra,
    I promise to buy your book (everyone probably will), can you helh the above me with the Multiselect question? And, are there these kind of examples in one your books? (I believe you have several).
    Thanks,
    Mark

  • Mark, a ListBox uses completely different code than what we're doing here, with data validation.

    For examples, see Dick Kusleika's post on Multiselect Listboxes:
    http://www.dailydoseofexcel.com/archives/2004/04/27/using-multiselect-listboxes/

  • Rick and George, I've recorded a video on setting up the data validation drop downs, and adding the worksheet event code.
    It will be posted on the blog tomorrow, so I hope that will help you.

  • Mondweep Chakravorty

    Many Thanks..very useful! :-)

  • Sameena

    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.

  • Rick Berrish

    Debra
    Thanks so much for the new video. I did get mine working but have encountered a new problem. All the sheets in my file have password protection. Without protection the code works great. When I apply a password it do not function. Is that the way it is suppsoe to work or do I need to change something?
    thanks

  • @Rick, make sure that the data validation cells are unlocked, before you protect the worksheet.
    In the code, you can add a line at the top, to unprotect the sheet -- include a password, if you used one:
    Me.Unprotect Password:="LOckEd"

    In the ExitHandler, turn the protection back on:
    Me.Protect Password:="LOckEd"

  • Rick

    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

  • Jen

    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!

  • Lyle

    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.

  • khevana

    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

  • Robert

    I am using Excel 2007 and can get the code to work, however when I save the file I get a msg indicating I cannot save the following features: VBA Project, in a "macro-free" workbook. I gives me an option to save as "macro-enabled" workbook however the file type changes to XLSM. Is that the correct way? Will that work for users that have Excel 2003? Thanks...r

  • @Robert, you can save the file as Excel 97-2003 format, and people with Excel 2003 will be able to use the file too.

  • Yvonne

    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!

  • Yvonne

    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

  • Luis

    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

  • Stephen

    Hi, I am trying to duplicate step by step your "Select Multiple Items from Excel Data Validation List" using Same Cell.
    I downloaded you DataValMultiSelect spreadsheet thought I could just copy and paste your code into a tab on my spreadhseet but cannot do it. Maybe I am not allowed to. That is ok. Is there a way to duplicate this easily. Do I have to pay? That's ok too. I am running Excel 2007

  • @Stephen, there is another video that describes how to add the code to your workbook, and adjust it, if necessary:

    http://blog.contextures.com/archives/2011/04/29/how-to-set-up-multiple-selection-excel-drop-down/

  • @Luis, you might need to change the code so it looks in all columns for the data validation drop down lists. This video shows how to modify the code:

    http://blog.contextures.com/archives/2011/04/29/how-to-set-up-multiple-selection-excel-drop-down/

  • Stephen

    Debra
    Thank you for this video. It works!

    Follow-up question:

    What if we wanted the same type of code listed within the same spreadsheet for column 3 and perhaps column 4 etc...
    In other words, with your code, column two accepts multiple responses, can column three, four and five do the same within the same sheet?

  • @Stephen, yes, you can change the code to work in multiple columns. Here is the revised "If Intersect" section:

    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
      Select Case Target.Column
        Case 3, 4, 5
            Application.EnableEvents = False
            newVal = Target.Value
            Application.Undo
            oldVal = Target.Value
            Target.Value = newVal
            If oldVal = "" Then
                'do nothing
            Else
                If newVal = "" Then
                    'do nothing
                Else
                    Target.Value = oldVal _
                      & ", " & newVal
                End If
            End If
      End Select
    End If
    
    • Stephanie

      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 :(

    • Charles

      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?

  • Greg - Australia

    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

  • Greg - Australia

    Just found another request, when you click on the drop down list is there a way that the whole list is shown and not just 8 items with a scroll down bar.
    Thanks

  • @Greg, thanks, I'm glad you like the article and video. I don't have a solution for 1 or 3, using this technique.

    For #2, there is a post that explains how to set up an editable sheet: Edit Multiple Selections in Excel Drop Downs

  • Greg - Australia

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

  • Stephen

    Debra

    Thank you so much for your code. You have been a tremendous help to me and I appreciate that.
    You vid was excellent and very clear to understand.

    Thanks again!!!!

  • @Stephen, you're welcome! Glad it helped.

  • David

    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?

  • dave

    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

  • Sylvia Binkley

    Very sweet code. Thank you so much!!!!! Full credit given.

  • Pat Green

    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

  • Riles

    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!

  • TB

    The code works perfectly as long as I don't try to protect my worksheet. I removed the code on the "ThisWorksheet" tab but I am still unable to protect the sheet. Any help would be greatly appreciated.

  • TB, make sure that the data validation cells are unlocked, before you protect the worksheet.
    In the code, you can add a line at the top, to unprotect the sheet -- include a password, if you used one:
    Me.Unprotect Password:="password"

    In the ExitHandler, turn the protection back on:
    Me.Protect Password:="password"

  • @Riles, if you're using the SeparateRows example, which puts the selects in the column to the right, you can change this line in the code:
    If Target.Offset(0, 1).Value = "" Then

    to this

    If Target.Offset(0, 2).Value = "" Then

  • KJW

    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.

  • KJW

    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

  • KJW

    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.

  • KJW

    Can more than one of the be used on the same workbook and on the same sheet? How do I put the code on the same sheet?

  • @KJW, you can change the iCol code, so it starts from column S, and looks to the left of that column:

    iCol = Cells(Target.Row, "S").End(xlToLeft).Column + 1

  • @KJW, you can combine the two options within the same Worksheet_Change code. Use Select Case, as in the Separate Rows sheet code.
    For example:
    Select Case Target.Column
    Case 8
    'code for same cell
    Case 10
    'code for separate columns
    End Select

  • KJW

    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.

  • KJW

    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?

  • KJW

    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

  • KJW

    Please help. I have been trying to modify the code based on information in this forum but to no avail.

    First I would like to use the Separated Columns in two places on my worksheet column J (10) and column AU. I have tried to modify the code:

    If Target.Column = 10 or If Target.Column = 43 Then

    That part worked but I would like to modify the iCol code, so it starts from column S and BH , and looks to the left of those columns.

    This works great but I just need a little help with the code. I have been trying to modify but not sure yet. Please help

  • @KJW, you can use Select Case, and two additional variables, to set the columns for the search to the left:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitHandler
    
    Dim rngDV As Range
    Dim iCol As Integer
    Dim iStopA As Long
    Dim iStopB As Long
    
    iStopA = 19
    iStopB = 60
    
    If Target.Count > 1 Then GoTo exitHandler
    
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then GoTo exitHandler
    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
      Application.EnableEvents = False
      If Target.Value = "" Then GoTo exitHandler
      If Target.Validation.Value = True Then
        Select Case Target.Column
         Case 10
          iCol = Cells(Target.Row, iStopA).End(xlToLeft).Column + 1
         Case 43
          iCol = Cells(Target.Row, iStopB).End(xlToLeft).Column + 1
         Case Else
          GoTo exitHandler
        End Select
        Cells(Target.Row, iCol).Value = Target.Value
      Else
        MsgBox "Invalid entry"
        Target.Activate
        GoTo exitHandler
      End If
    End If
    
    exitHandler:
      Application.EnableEvents = True
    
    End Sub
    
  • rmk

    Firstly: thanks for an excellent code! Secondly: something's terribly wrong out there... I use Excel 2010 and SameCell code that is working perfectly as long as I myself open the file (saved in .xls format). Why, oh why, it does not work for anyone else opening the same file? The list and the data validation works fine but no multiple choices possible. Not on Excel 2007 nor 2010. Could you help, please?

  • @rmk, can the other users run any other Excel macros? Perhaps their security settings are High, and that's blocking the macros, without any warnings, on their computers.

  • SAC

    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.

  • Heidi

    Hello Debra,
    I have downloaded the workbook and tried using it before even copying it to my sheet and it does not appear to be working on the workbook I downloaded. The one I am interested in using is the fill down a column option.

    Thanks for your help,
    Heidi

  • @SAC, select the cells with the drop down lists.
    On the Excel Ribbon, click the Data tab
    Click Data Validation, and you'll see the list of items in the Source box.
    You can change the list there, then click OK to save and close.

  • @Heidi, you might need to change your macro security settings, and then allow macros to run in the workbook.
    There are instructions here: Excel macro security settings

  • Dan

    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?

  • Ubaid

    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 !

  • Brian

    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.

  • Steve

    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,

  • Contextures Blog » Excel Drop Down Multiple Select or Remove

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

  • Sam

    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

  • Sandra

    I came across your code and it works wonderfully. Thank you. I am using the code which allows multiple selections from a dropdown list separated by a comma in my worksheet. This works fine as my worksheet is unprotected. I need to have this functionality (protection) because I have formulas that I want to hide and cells that I need to lock. Anyway to get these requirements working together? Thanks in advance!

  • hello,,
    I have downloaded the workbook and using it before copying to my sheet and does not appear to be working on the file. The one I am interested in using is the fill down a column option. but i use vba with excellent. but it's problem now..

    you can help me please..

    thankou..
    tamer nagah elasid ali .,
    +2 01004611058

  • Alyssa - Australia

    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

  • simon

    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

  • Amar

    The file won't download anymore...

  • @Amar, thanks, the link should be okay now.

  • Kaysie

    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

  • Jill

    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.

  • Lyle

    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.

  • Lori

    I work on a Mac and therefore have Excel 2008 for Mac. I can't seem to use your tip to cut and paste the code to allow for multiple selections in data validation because when I right click the tab in my worksheet, there is no 'view code' option. Is there another way to do this in Excel 2008? (Note, my knowledge of Excel is extremely limited.) Thanks.

  • @Lori, as far as I know, you can't use programming in Excel 2008, so the multiple drop down code won't run in that version.

  • @Lyle, in Excel 2010, you might need to change your security settings, then enable the macro when the file opens. There are instructions here:

    http://www.contextures.com/excel-macro-record-test.html#ReadyTest
    http://www.contextures.com/excel-macro-record-test.html#Test

  • Luis

    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.

  • Dan

    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!

  • Charlotte

    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!

  • Wendi

    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?

  • Anna

    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

    • Juli E

      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

  • Blanca Beatriz

    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

  • Kim

    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.

  • Brian Hamilton

    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

  • AlexH

    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

  • MelanieM

    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.

  • MarkJ

    Hi Debra,
    I used your code to enable selection of multiple values in the same cell, and it worked perfectly thanks..
    But I'd like to be able to protect other cells in the worksheet, at the moment this stops the VB running so I can only select a single value again. Could you give me any tips on how to modify the code so that the VBA still runs when the sheet protection is turned on.

    Thanks so much.

    • @MarkJ, at the start of the code, you can turn off the protection, then turn it on again, at the end of the code.
      For example:

      ActiveSheet.Unprotect Password:="yourpwd"

      'code here

      exitHandler:
      ActiveSheet.Protect Password:="yourpwd"

      • Jim

        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?

  • Amber

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

  • James

    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.

  • Dannie

    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.

  • Nancy

    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

  • Nancy

    Debra, forget my last message. I've seen that you had an example of the one with the line break. I juste downloaded it. Copied it into my worksheet VBA code but... nothing happenned. Is there something I should be changing?
    Thank you
    Nancy

  • Flippa

    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

  • Don

    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.

  • Don

    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?

  • Don

    hmmm....I ran across a comment that the SpecialCells function would work with only up to 8,192 non-contiguous cells - I'm definitely exceeding that. Could this be the problem? This was on the Microsoft site - it also states that no error message will be returned within VBA code - and that this is per design. thoughts?

  • Don

    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.

  • Don

    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.

  • TinaW

    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?

  • BillJ

    Hi Debra,

    I am sorry, but I am a newbie with this. Everything you offered above is clear but when I downloaded the sample data validation file into Excel 2010 and go to each tab to try the different options, the cell I select changes, i.e. 1.2.3... but it does not do what the code says, i.e. put in next cell, same cell with comma, .....

    Is there something else I need to do to see each of the samples work?

    thank you for your help.

    Bill

  • BillJ

    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

  • Juli E

    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?

  • David

    Good morning, Debra:

    Thank you for the great code!
    I have been playing around with your "SeparateRows" code.

    Just a few questions:
    1)I would like to insert the validation menu at cell M2 only. Also, I would like to have the items appears from M3, M4, M5 and onward. How should I achieve this?

    2)In addition, how can I set a limit to the number of rows that the code will populate? For example, I only want users to be able to populate M3, M4, M5, M6, and M7. If the user wants to populate past M7, an error message will pop up.

    Thank you for your help in advance.

    Sincerely,

    David

  • I had trouble with the locking of the sheet as well.

    But here's something that's worked for me:

    I've simply added the Me.Unprotect to both the top and bottom of the code.

    I can lock and unlock my sheet and it works fine. I do not have a password of my sheet however.

    Hope it helps some.

    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
  • Juli E

    Triplets,

    The Me.Unprotect without a password works, but it promts for a password and you have to click okay without putting a password in. That would be fine for me, but I am sharing this spreadsheet with over 100 employees. It is not "fluid" enough to use that option. Do you know of other options that would allow part of the spreadsheet to be protected and the part where the I have my VBA code for my multi-item dropdown lists to be unprotected?

    Any suggestions are apprecaited.

    • @Juli E - what version of Excel are you using? If you protect the sheet, with no password, you should be able to unprotect it without a password prompt appearing.
      Also, if you unlock the data entry cells, before protecting the sheet, users should be able to select multiple items in those cells, even if the sheet is protected.

      • Juli E

        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!

  • Matt

    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

  • JanetD

    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?

  • Betsy

    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.

  • Michael

    Hi Debra, your website is great. I've copied your "SameCellAddRemove" code. I've read above on how to limit or expand the code from one column to all columns. How do i go about limit to three specific columns, for example, columns 7,14 & 21? For example: If Target.Column = 3 Then

    How do you change it to include 7,14 & 21?

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

      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
  • Michael

    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
    • @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
      
  • Michael

    It works! Thank you!! You are amazing. Have a good day

  • Raheel

    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.

  • Michael

    I'm using your samecell multiple selections VBA code and I'm trying to apply it to drop down lists throughout a spreadsheet. I've been able to use the CASE statements to get most working but it occurred to me I have more drop downs selected than excluded. For example, I have many lists where multiple selections should be allowed but not drop downs like "male/female" or "yes/no". Is there a way to let the code apply to the entire sheet and simply exclude the few lists you don't want it to apply to?

    The code is fantastic but I'm just trying to tweak it to accommodate my needs a little better. Thanks.

    • @Michael, you could put the columns that you want to exclude in one Case statement, and the multi-selection code in the Case Else section. For example,

      Select Case Target.Column
      Case 3, 4, 6
      'do nothing
      Case Else
      'multi-selection code goes here
      End Select

  • Raheel

    Debra it working now. Thank you very much for your support.

  • Michael

    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.

  • Josh

    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.

  • hb

    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

  • Greg

    Hi Debra,
    Great information. Is it possible to alphabetize and/or sort the result order of a same cell (separated by a comma) or same cell line break?
    For instance, the result "A, B, D, E, C" is out of order, can the result be sorted to alphebetize within the the same cell result?

  • Greg

    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

  • Kevin

    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

  • Abdul

    Hi i want to give hh:mm - hh:mm format in excel cells how do i do that, please do the needful

  • Ryan

    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?

  • Phil

    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.

  • Drew

    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?

  • [...] I have applied a macros Hi, I recently added a macro published online from Contextures (Select Multiple Items from Excel Data Validation List | Contextures Blog) that allows multiple items to be selected from a dropdown menu into one cell, however I specified [...]

  • Juliana Arias

    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

  • Jason

    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?

  • Rob

    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.

  • Mike

    Hi Debra, Just to start off you're amazing. I've go a problem with images in comments and I was hoping for some help. I've been dabbling with creating a macro that allows a user to select an image from "my pictures" and then automatically inserting that image into a cell comment. Unfortunately, while the macro works great, we underestimated the popularity of the spreadsheet and now there are hundreds of pictures and the file has become too large. Is there a way to copy the image from the comment into a file structure and then replace the image in the comment with a link to the image's new path. Not sure if this can be done but I need to do something to manage the size of the spreadhseet while still allowing images to be identified with the cell. By the way, your site is fantastic and has taught me a lot. Thanks!

    • Thanks Mike! Glad the info is helping you.
      I don't have an example that extracts pictures from comments, but there is sample code in this old newsgroup posting that might help you get started: Excel Comment Pictures
      It's for a single comment, and copies a picture of the comment.
      You'd need to tweak it to go through all the comment cells, and remove the picture, then add a link as the comment text.
      The link wouldn't be clickable though, so it might be better to put the link in an adjacent cell.

  • John

    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.

  • Sarah

    Hi Debra,

    I apologise now if I'm duplicating this query, but I have a quick question about multiple selection with a comma separated and pivot tables? I used your coding to allow users to choose the tools they used to carry out research, which also allowed them to make multiple choices that would separate the items with a comma, and it works a treat.

    The only problem is, when I try to add this data to a pivot table, it treats all items in the list as different variables. Although there are 6 items in the list, when it comes to counting how many times they used those tools, it won't count how many times those items appeared in that column, but will consider each combination of multiple selections as one item.

    Is there a way of over riding this so that it just lists the number of times each of those tools were selected?

    Thanks.

    • Hi Sarah, unfortunately a pivot table won't be able distinguish the unique items within a single cell.
      Instead, you could use a COUNTIF formula to count the occurrences of each item in your data validation list.

      • Gretchen

        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?

  • IRa

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

  • Edward

    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?

  • Nalin

    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

  • Jane

    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.

  • MKH

    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!

  • Craig

    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

  • jbcorcor

    This is amazing, thank you!!!!!

  • hami

    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

  • Bullpen

    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?

  • angela

    Hi,

    this is working fine except if i protected the sheet.
    Please help.

    Thanks

  • Julie

    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

  • bbouvier

    I downloaded the example spreadsheet and when I opened it in Excel 2010, I received the big red "Protected view" warning: Office has detected a problem with this file. Editing it may harm your computer.

    This makes me distrust the file. Well, its also possible that I just sent my e-mail address to all the spammers in the world by posting this reply.

  • bbouvier

    Thanks for the pointer to the Microsoft Excel team's blog. Everything worked as designed. Now, I need to figure out how to make multiple values work in conjunction with dependent validation lists. For example, if column C is "State", and column D is "County", the '=indirect' function would make the County validation list display only the counties in the State. However, the =indirect function fails if you select two states (e.g. California and Nevada) since there is no range named 'California, Nevada'

  • Debra
    If data validation is used, can the cell still be used for free text?

  • Fran

    This was super useful. Thank you so much!

  • Reema

    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

  • yisroel

    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!

  • Janne

    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

  • Guillaume

    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

  • Clive

    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.

  • Lisa

    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?

  • Trent

    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

  • Pramod

    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

  • Louisa

    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

  • Ed Fairbairn

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

  • Christopher Jones

    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

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>