Excel Drop Down Multiple Select or Remove

There’s a popular sample file on my website, that lets you select multiple items from a data validation drop down list. Since the original article, I’ve posted updates:

And here is the latest update.

Remove Previous Selections From Cell

In the comments on the original post, Dan asked for a way to remove items if they been previously selected. For example, the cell contains four items — “One, Two, Three, Four”.

datavalmulticellremove01

When you select Three again, that number is removed from the cell, instead of being added at the end of the cell.

datavalmulticellremove02

Download the Sample File

To experiment with this technique, you can download the sample file from the Contextures website, on the Excel Data Validation – Select Multiple Items page. The file is in Excel 2003 format and zipped. There are macros in the file so enable them to test the features.

The new sample is on the SameCellAddRemove worksheet.

Please let me know in the comments if there are other features you’d like to see in this workbook. Thanks!

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 Excel Tutorial Video

This video shows how the multiple select and remove features work. You can also see the Excel VBA code, and the steps that it goes through, when you make a selection in the drop down list.

________________

You may also like...

35 Responses

  1. brian says:

    Hi -Is it possible to have each menu item when selected appear on a separate line instead of separated by a comma
    ?
    Thanks in advance,
    Brian.

  2. Lisa Damico says:

    I have a quick question…I found out finally how to be able to add more than one selection to the cell, thank you very much for your help. However I have one last question about removing if you accidentally hit a selection that you do not want in that cell? So my question is even after watching the video, I am having difficulties. Can I combine all these codes into one worksheet that I need all these things to happen:
    Adding more than 1 selection
    Removing any additional selections I might have chosen.

    How do I go about that adding all these codes into one worksheet, or do I need to keep them separate on their own worksheet, but then how do I get all these things to work on a spreadsheet column? Please help. Thank you so much.

  3. Anisa says:

    How could I make the font style of the 1st selection to be (Bold) ” One, Three, Four” (one)to be Bold in this example . What is the suitable code for this to be added with the multiple selection drop down list code?

  4. IRa says:

    Also, what if i have more then two cells with drop down menu and i want that all my cells get a multiple selection too?

  5. IRa says:

    while all my cells can have multiple selection, can i have one cell with only one selection?

  6. EriK says:

    I am unable to get your workbook to work properly, specifically the SameCellEdit, but nothing appears to work.

  7. Becky says:

    I am an old hand at Excel and VBA and doing multi-select picklists is not something I would generally do in Excel. But sometimes Excel is the only tool at hand and it is all about imagining how it can be done. Worked like a champ; even handled the old multi-select data I downloaded from Salesforce.

  8. Rima Lahiry says:

    Your tutorial sessions are fantastic.
    Just wondered do you know how to add more than 1 SameCellAddRemove columns on 1 worksheet.

    I need 3 columns with this samecell add remove feature on same worksheet/

  9. Edwin Briggs says:

    This formula works brilliantly, thanks. Now my only problem is that it doesn’t work if the worksheet is protected, even if the actual cell containing the dropdown list is unlocked. It just behaves like a normal data validation list, i.e. the code is ignored.

    Is there any way to allow it to work even if the sheet is protected?

  10. Edwin Briggs says:

    Found the answer! Had to add this macro into ThisWorkbook:
    Private Sub Workbook_Open()
    ActiveSheet.Protect Password:=””, UserInterfaceOnly:=True
    End Sub

  11. Ken Roberts says:

    This formula/code works great, except when the list of items for the drop down contains common text across more than one item (e.g. list of items contains “Agree”, “Strongly Agree”, “Disagree”, Strongly Disagree”).
    In this example, after selecting “Agree” and “Strongly Agree”, the cell where the drop down list exists is populated with “Agree, Strongly Agree”. However, when re-selecting “Agree” again to remove it (unselect it, if you will), the cell is left with “Agree, Strongl”.
    Is there a way to get around this (unfortunately the options have to be the text listed above)?

  12. ahmed says:

    till now i dont know how to make this function please i need easy way to make it .

    thanks

  13. Rodrigo says:

    @Debra, thank you very much for this “tool”. I downloaded the sample excel sheet and I was wondering how can I merge the code for “SameCellAddRemove” and “LineBreak”. I would like to use both functionalities.

    • Rodrigo, in the code for the SameCellAddRemove, change the “, ” separator to vbCrLf. There are 2 lines where that occurs:

      Target.Value = Replace(oldVal, newVal & vbCrLf, “”)
      and
      Target.Value = oldVal _
      & vbCrLf & newVal

  14. Rodrigo says:

    Thank you very much, it work!!!

    I really appreciate your time and assistance with this.

  15. Andy says:

    Hello there,
    I’m using the code below and it works well. The code applies to the whole worksheet as I have multiple drop downs that I want it to work on. The only issue I have is that I would like to remove the ability to select one particular word on the drop down list alongside any of the other options. In other words, by selecting another choice this word no longer appears in the cell. (The word is “Select”). Many thanks for your help.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ‘ Developed by Contextures Inc.
    http://www.contextures.com
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    If Target.Count > 1 Then GoTo exitHandler

    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Intersect(Target, rngDV) Is Nothing Then
    ‘do nothing
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If oldVal = “” Then
    ‘do nothing
    Else
    If newVal = “” Then
    ‘do nothing
    Else
    Target.Value = oldVal _
    & “, ” & newVal
    ‘ NOTE: you can use a line break,
    ‘ instead of a comma
    ‘ Target.Value = oldVal _
    ‘ & Chr(10) & newVal
    End If
    End If
    End If

    exitHandler:
    Application.EnableEvents = True
    End Sub

  16. Greg says:

    Hi –

    Im getting a run-time error anytime I change something in a cell outside the target column. Any thoughts?

    • Tzvi says:

      That’s surprising. I would have expected the code

      If rngDV Is Nothing Then GoTo exitHandler
      
      If Intersect(Target, rngDV) Is Nothing Then
         'do nothing
      Else
         //do stuff
      End If
      

      to only respond to that column. Confirm that your code has not been altered.

      Can you post your error details here? (Err.description and Err.Number)

  17. Llewellyn says:

    As with Edwin’s request, I’m trying to get this code to work on a password protected sheet. I am not getting it right with the code he provided – please help!!

    Thank you.

    Llewellyn

  18. Tzvi says:

    @Debra Thank you very much for the code. I’m now using it all over. I do have a couple questions though.

    1) After the values are changed to “Choice1, Choice2, Choice3”, I’m getting a data validation error. Naturally, the dropdown has three individual choices, neither is “Choice1, Choice2, Choice3”. Must I suffer through this error or is there a way around it.

    2) If I type in an item into the box, it will receive it as if I selected it with the mouse. It is case-insensitive though and I may end up with “Choice1, Choice2, choice3” or even “Choice1, Choice2, Choice3, choice3”. Do you have a workaround for this.

    3) If I start to type in something and then click the dropdown arrow expecting to select an item, excel immediately tries to validate my partial entry and complains “Invalid entry, [Retry], [Cancel]. If I click [Retry], fine. If I click [Cancel] I get a runtime error “Method Undo Failed”. Is there some code that can avoid this issue?

    I thank you again for sharing your expertise with the rest of us.

  19. John says:

    How do I use SameCellAddRemove code for more than one column? Ex. I currently have it being used for column 12 but I’d also like to apply the multiselection to column 10.

    Thanks!

  20. Coder says:

    I am currently using the code that allows multiple selections in a cell. However, I am having an issue with the code. Whenever there is data selected in the cell and I double-click in it and then click out of it, whatever was in the cell disappears. This is a huge issue since I cannot undo the deletion of the data in the cell, and there is no other way to recover the data. How can I fix this?

    Please help! Very urgent.

    • @Coder, in the sample workbook, use the code from the SameCellEdit sheet. If the “Edit Entries” box is checked, it stops running the event code. You can make your changes, then uncheck the box.

  21. Candice says:

    Debra, I am new at this and would like to combine several of these for use on a worksheet (not just a column). How would I enter the code to allow for multiple choices in a drop down, no duplicates, and a line break instead of a comma?

    • Candice, make the following changes to the code in the SameCellNoDups example:
      1) Change this line, that uses the comma and space separator:

                 & ", " & newVal

      to this, which uses a line break:

                    & Chr(10) & newVal

      2) Change this line, which limits the code to column 3:

            If Target.Column = 3 Then

      to this, which allows the code in any column:

            If Target.Column > 0 Then
  22. Corey says:

    Following Candice’s comment, is there a way that I can get it to skip two lines ie. have a space between selections that fall one under the other in the same cell?
    Secondly, is there a way to make amendments to the text that is added in the editable cell. Ie. I select something from the drop down in bold but then the text I enter is unbolded?

  23. Corey says:

    Just an update I figured out the spacing but would still like to know how to change the font and perhaps the height of the line break as well. Thank you

  24. Corey says:

    When I delete something that was selected in error and the click off of the cell it keeps the old selections and then doubles the whole thing with the edit done. So If I removed a drop down list that said Mark, Jason, David and David was selected in error and I deleted it I would get:

    Mark
    Jason
    David

    Shows as
    Mark
    Jason
    David
    Mark
    Jason

    How can I resolve this. If I try to take duplicates out it simply doesn’t work. I was able to get it to work but then I don’t have the other options. Please help with this.

    • In the sample file, on the SameCellAddRemove worksheet, there is an example that lets you remove items if they been previously selected. There is also a worksheet that shows how you can add an Edit option, and turn that feature on or off.

Leave a Reply

Your email address will not be published. Required fields are marked *