Excel Drop Down Multiple Select or Remove

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!

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.

________________

45 thoughts on “Excel Drop Down Multiple Select or Remove”

  1. 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. 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. 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. 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. I am unable to get your workbook to work properly, specifically the SameCellEdit, but nothing appears to work.

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

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

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

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

    1. Hey Edwin,
      Thanks for the code. I’m still new to vba, I cannot get the password unprotect and protect to work – can you share your code please, or how do I incorporate this code into the multiple drop down code?
      Thank you.
      Llewellyn

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

    1. @Ken, I’ve added a new worksheet — SameCellAddRemove2 — in the sample file, and it avoids the problem of removing similar entries.
      The premium version of the technique also avoids that problem, by showing a list of the selected items. Just remove a check mark, to remove that item.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  22. Hi Debra,
    I am 100% new to this whole coding aspect of life. I have 2 questions. The first is that i’m having difficulty with the removal of an entry in the code. If I’ve selected multiple entries in my same cell and i don’t need one of them or it was accidentally selected, i can’t get the removal code to work. I’m positive i’m entering it wrong…
    The 2nd question is am i able to set this to work on multiple columns in a single work sheet? So where we have entered “Target.Column = #” is there a way to apply this to say columns 8 and 2?

  23. The VBA code only works in one cell. What do you do if the column contains several drop down lists and you want to be able to select multiple values from each list?

  24. Thank you for sharing this very useful tool. I have found success when I am just creating a data validation in a new spreadsheet. However, I am needing to use it in an existing spreadsheet, which has multiple worksheets, vlookups, and existing data validation. The main worksheet is in a table format. I thought that may be an issue, so I removed the table format, but it didn’t seem to make any difference. The spreadsheet is not locked either. Are there any issues when using an existing spreadsheet that already uses vlookups or multiple data validation lists? I have used the both the SameCell code and the SameCellAddRemove options. Both only allow the one entry. I have also made sure I changed the code to include the correct column. Additionally, I have tried adding code in for many columns and removed the code for columns to allow for any data validation columns. So far, no luck. Any assistance would be appreciated.

  25. Hello,

    Was wondering if you could assist please…

    I have the below code in Excel from “Excel Drop Down Multiple Select or Remove”. The sheet is now protected and the code no longer works.

    1) What line do I change to allow the multiple select to work.
    2) I’d like a line break instead of comma and if possible, for the multiple items to appear in A-Z order (if possible).

    Any suggestions?

    Thanks,

    James

    1. Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rInt As Range
      Dim rCell As Range
      Dim tCell As Range

      Set rInt = Intersect(Target, Range(“A:A”))
      If Not rInt Is Nothing Then
      For Each rCell In rInt
      Set tCell = rCell.Offset(0, 1)
      If IsEmpty(tCell) Then
      tCell = Now
      tCell.NumberFormat = “dd/mm/yyyy hh:mm”
      End If
      Next
      End If

      ‘ Developed by Contextures Inc.
      http://www.contextures.com
      Dim rngDV As Range
      Dim oldVal As String
      Dim newVal As String
      Dim lUsed As Long
      If Target.Count > 1 Then GoTo exitHandler

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

      If rngDV Is Nothing Then GoTo exitHandler

      If Intersect(Target, rngDV) Is Nothing Then
      ‘do nothing
      Else
      Application.EnableEvents = False
      newVal = Target.Value
      Application.Undo
      oldVal = Target.Value
      Target.Value = newVal
      If Target.Column = 3 Then
      If oldVal = “” Then
      ‘do nothing
      Else
      If newVal = “” Then
      ‘do nothing
      Else
      lUsed = InStr(1, oldVal, newVal)
      If lUsed > 0 Then
      If Right(oldVal, Len(newVal)) = newVal Then
      Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) – 2)
      Else
      Target.Value = Replace(oldVal, newVal & “, “, “”)
      End If
      Else
      Target.Value = oldVal _
      & “, ” & newVal
      End If

      End If
      End If
      End If
      End If

      exitHandler:
      Application.EnableEvents = True
      End Sub

      1. Hi Debra,

        Thanks for your reply, I’ve managed to get the protection issue sorted.

        Regarding a line break, when I remove an item that is already in the list, it removes a letter from the previous line, for example:

        Selection 1
        Selection 2
        Selection 3

        would turn into:

        Selection 1
        Selection

        Many thanks,

        James

  26. Hi,

    Is it possible that I want to be able users to choose multiple “Team” and show all Tasks under that Team; and at the same time be able to further choose multiple selection under “Task”:

    Team Task # of Docs
    A Editing Docs 3
    A Proofreading Docs 2
    A Final Draft Docs 0
    A Printing Docs 2
    B Editing Docs 2
    B Proofreading Docs 5
    B Final Draft Docs 6
    B Printing Docs 0
    C Editing Docs 3
    C Proofreading Docs 2
    C Final Draft Docs 2
    C Printing Docs 0

    Please help because as of the moment I have to input Team letter on each row, so that when I filter via the Team column, it shows the data on the following column

Leave a Reply

Your email address will not be published.

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