Comments on: How to make Excel drop down list with multiple selections

By default, Excel data validation only allows users to select one item from a predefined list of options. But fear not, as there is a solution. By using VBA, you can create drop-down lists with multiple selections. Continue reading →

Comments page 2. Total comments: 705

  1. I keep getting this error which says " The source currently evaluates to an error.do you want to continue?

    1. Hi! Without seeing your data it is impossible to identify the exact cause of the error. The error means the formula you used for data validation isn't working correctly. This could be because of a mistake in the formula itself or because it references something that has an error.
      Common Causes:
      Incorrect Named Ranges: Data validation often uses formulas that reference named ranges. If the named range you're trying to use doesn't exist, is misspelled, or contains spaces, the formula will have an error.
      Open the "Name Manager" (Formulas tab -> Defined Names -> Name Manager). Verify the named ranges you're using exist, are spelled correctly, and don't contain spaces.
      Make sure the cell containing the named range reference in the INDIRECT formula is spelled exactly the same as the named range itself.
      Ensure no extra spaces or characters are in the reference cell.
      INDIRECT Function Errors: If your formula uses the INDIRECT function, it might be trying to reference a cell that doesn't contain a valid named range name. There could be typos or extra spaces in the reference cell.
      Even if you aren't using named ranges, the source formula itself might have a syntax error, typos, or incorrect cell references.
      If not using named ranges, examine the source formula itself for syntax errors, typos, or incorrect cell references. Consider using Excel Evaluate Formula feature. (Formulas tab -> Formula Auditing -> Error Checking).
      If all else fails, try clearing the data validation rule and setting dropdown list up again. Sometimes a refresh can resolve the issue.

  2. This guide really helps me a lot.
    Really Appreciate the effort.

    I have a question to ask.
    I want to add color-coded dropdown system on top of code under "Multi-selection dropdown with item removal" area, to highlight elements already been selected.
    What I want to do is color listed elements within the dropdown menu, not the cell itself.
    But all guide I search through online is about color coding the cells. I cant seem to find a case that relating to this topic.
    Is it possible to add color or highlight to elements in dropdown menu?

  3. Alexander,

    This VBA works great, I appreciate the write up. I am using the "new line multi selection" with the vrbCrLf function and it is working within the cells. However, the cell only displays the first selection made from the list. I have to manually click each cell to show the items displayed. I have the rows formatted to Autofit Row Height etc. I am not sure what is stopping the cells from displaying all the contents selected.

    Could you provide me with some insight on what might be working against me?

    Regards,

    Kenyon

  4. Hi, I am using the multi-selection with item removal. I have a column "Departments" and I can now select one or more departments at a time.

    It's working wonderfully, except for the fact that I am getting a data validation error for any cells where I've selected multiple items. Is there a way to stop this from happening? I saw someone else just turned off errors completely, but I don't think that's necessarily what I want.

    I also would love to able to sort/filter but Excel is considering multi-selections as their own unique filters. Will I just need to use a text filter ("Contains") instead?

    1. Hello Karli!
      I can't see your data, so I can't say exactly what the cause of the error is. Carefully read the warnings and tips in the article above. Please note that you cannot use delimiter characters inside dropdown list items.
      A dropdown list creates a text string. So to filter on a single word, use the “Contains” Filter option.

  5. I got the drop down to do multi-select (wonderful) but when I execute the "Advanced" filter to actually extract the data to another sheet, it comes back with no data. Selecting one item and it appears fine.

    My program works by having the user selection in its own tab and when "Get Data" macro invoked, it copies it and transposes to the 2nd line (beneath the headers) of the output sheet - and two further lines down is the "Output" range. All very basic and nothing out of the ordinary - and, as I mentioned, works just great with one item per selection.

    Add a 2nd selection though, it comes back with nothing at all. Can't work it out at all.

    Help! (Please)

    1. Hello Mark!
      The multiple select macro uses a drop down list to create a text string that consists of multiple list items. I have no way of knowing how your macro and advanced filter work with this text string.

  6. Thank you for the amazing VBA! Is it possible to allow manual text entry that doesn't contort the multi-select entries? When I add a manual text entry after using the drop down selection, it messes up the drop down selections.

  7. Hi, this worked great. Is there a way to only have it limited to one column? There is one column of information I need to be multiple select and my other columns that have drop down lists need to be single choice items. How would the VBA script change? I've used AI repeatedly to work through this and cannot come up with a solution that doesn't give me an error.

    1. Hello Emily!
      If you had read the article carefully, you would have found the answer in this section: Multi-select dropdown for specific columns, rows, cells and ranges.

  8. Is there any way to copy and paste the worksheet into another worksheet without losing the VBA code? Or do I need to reset it each time?

    1. Hi! Unfortunately, there isn't a direct way to copy and paste a worksheet with its VBA code intact to another worksheet within the same workbook.
      VBA code is tied to the specific worksheet module, and copying the worksheet itself doesn't automatically transfer the associated code.
      However, there are a few workarounds you can consider:
      1. Exporting the VBA Code:
      Open the source workbook and press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
      Insert a new module by clicking Insert > Module.
      Copy the VBA code from the source worksheet's module.
      Paste the VBA code into the new module in the destination workbook.
      2.Copy the worksheet to a new workbook.
      The VBA code should be transferred along with the worksheet.

  9. Hey, This has been working great thank you. I have one issue that I cant figure out though. There seems to be a max on the number of characters that can be added to a cell when selecting multiple drop downs. When this happens it results in the cell turning into ############. The list I am using is a series of sentences so each item has a large number of characters. Is there a go around?

  10. How do you get this to work for online excel? OR is it not possible?

  11. Everything works, but the multi-select no longer works once I save the document! What am I doing wrong? I'm saying it as a Excel Macro-Enabled Workbook (*.xlsm). Please help! Thank you!

  12. Hello, is there a way we can highlight selected items from the dropdown list to avoid getting it removed the second time it's selected?

      1. Oh, I mean we select items from the list right? For example, a list of fruits: Apple, Banana, Mango, Lychee, Orange. I am selecting Apple, Banana, and Orange only from the dropdown. Is there anyway these words can get highlighted from the dropdown just to show that they are already selected/added to the list?

  13. This helpful by far dude, I couldn't help to say it out loud... but how to apply it in every single sheet inside of a workbook?
    Should I apply it over every sheet making use of Alt + F11 and double clicking on the desired sheet and adding the code again and again?
    That's kinda exhausting and frustrating in a limited way.
    By the way, +10 and reco for the post mate.

    1. Hi! I wrote earlier in the comments that the Worksheet_Change event in VBA (Visual Basic for Applications) is used to perform certain actions based on changing cell values in a worksheet. However, it only works on the current worksheet. Therefore, the code needs to be installed on each of the worksheets in which it is needed.

  14. This is so helpful! Is there a way to make a dropdown menu that also allows custom text input into a given cell? When I add custom text, the values already in a cell are duplicated.

      1. Thank you

  15. Is there a way to add a count function when allowing duplicates? That is to say, if I selected Apricots 3 times is there a way to have it display Apricots(3)??? I hope this makes sense.

  16. I searched high and low to try work out how to do Multi-selection dropdown with item removal AND How to enable multi-selection functionality in protected sheet. I found the following workaround:

    Forget adding the above code for "How to enable multi-selection functionality in protected sheet" mentioned in the article if you are having issues using it and just add "ActiveSheet.Unprotect Password:="Password" & "ActiveSheet.Protect Password:="Password" in the following places within the code:

    If Destination.Count > 1 Then Exit Sub
    On Error Resume Next
    ActiveSheet.Unprotect Password:="Password"
    Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitError

    AND

    end of the code

    exitError:
    ActiveSheet.Protect Password:="Password"
    Application.EnableEvents = True
    End Sub

    Hope that helps :)

  17. Alexander,

    This is awesome, having an editable list of items in a data validation field is something my internal customers require. I appreciate both the example workbook and the VB behind it. I have learned a ton.

    However…
     
    I have implemented the "Item removal" version in my workbook and it does what I want. However, if I use that data validation in a cell of my own table I get a Data Validation alert as soon as a second value is chosen.

    Very simple example. Workbook with two sheets. Sheet two contains the table "Years" and consists of a single column "Years". The rows have a single year, such as 2024, second row 2025, and so on. Sheet one contains a two column table. First column is "Thing", second column is "Year(s)". The data validation is List with "=INDIRECT("Years[Years]")" as the Source. One reason I want to use a table is that there can be any number of rows from 49 to over 12,000. But I'd rather not set every row up in advance. Do you have any suggestions?

    1. Hello Lorin!
      Unfortunately, I was not able to reproduce your problem in my workbook. And I can't check how the drop-down list works in your workbook and whether the macro code is set correctly.

    2. I found a way to ignore all errors by putting:
      Application.ErrorCheckingOptions.BackgroundChecking = False

      In the workbook open event and
      = True

      in the Before Close event.

      I don't love this because I don't want to be changing global user preferences for them.

  18. Hi, Thankyou for sharing this. I have been using the ‘Multi-selection dropdown with item removal’ code on my worksheet successfully! I am now setting up a Data Entry User Form and was wondering whether I can use this code to have a Combo Box behave in the same way?

      1. Thanks Alexander.
        Are you aware of any ways or able to point me in the right direction as to how I might get this or similar functionality with a user form? Otherwise I am thinking I would need a separate box for each instance to collect the data.
        Regards.

  19. Hello Alex,
    I am really grateful for the information that you have provided, in such a detailed manner. Something that I would also like to know is that is there any code that we can modify/write to limit the number of selections made by the user?

    1. Hi! You can try limiting the number of separators that are used in the results of a dropdown list with multiple selections.

  20. Thank you, appreciate the information and level of detail.

  21. Hello, I am trying to do multi-selections in a protected sheet but I am getting an error (Ambiguous name detected: Worksheet_SelectionChange). I think I may be putting the protected sheet code in the wrong place. Can you assist me in combining the multi selection and protected sheet code? Thanks!

    1. Hello Lauren!
      I am unable to insert the code into your workbook. Carefully follow the instructions given in the article. Also, this issue has been discussed many times in the comments below.

  22. Hello,

    Thank you for sharing this guidance and code.

    Is there a way for the COUNTIF function to pick up when an option has been selected amongst others? For example, on your scenario to pull a count for how many times egg and cheese were selected in total?

    Thanks
    Claire

  23. Thank you for this great tutorial!

    I am working on using it in a table with tasks where ressources are selected for each task. I would like to use the drop-down list for the ressource(s) for each task. But when I copy the cell with the list (or drag the fill handle), I only get the value in the next cell and not the list. IS it possible to copy the cell with the list to other cells and still keep til dropdown list in the new cell?

    I have used the vba code for "Multi-selection dropdown with item removal" with DelimiterType = vbCrLf.

    Thank you for your help!

  24. I tried this and can still only select 1 item.

  25. Hi, thank you so much for this important code and explanation. But I would like to know how to connect the multi-select drop down list (which includes 3 names for an example) automatically with another cells. Let's say that I have a database table includes names (column A) and salaries (column B), how can I make a multi-select drop down list for names (column C) and the next cell (column D) shows their salaries in the same sequence automatically?

  26. Hello! It worked, but... I'm no coder, just a rookie and while I got the multiple options on the list to work all right (very excited), when I tried to remove them again they kept reappearing. I selected the option where you can remove them again afterwards. I have clicked on item in the drop down a second time and tried removing them manually.

  27. Fabulous - thank you!

    I implemented 'How to create dropdown with multiple selections in separate lines'
    and 'How to make a multiple selection dropdown with custom delimiter'

    Icing on the cake would be to allow fill-ins as well as multi-selections - can this be done?

  28. This is VERY helpful! Is it possible to alphabetize the list in the cell after each selection?

    1. Hello Krissi!
      In order to sort the words in a text in a cell that has been created using a drop-down list, you need to use a special VBA macro.

      1. Hi! Amazing post! Saved me a lot of time :)
        I just wanted to follow up on Krissi's comment. What would be the addition to the existing VBA macro that we inputted to create the multi-select dropdown so that the selections sort alphabetically and not in the order they were selected.

        Thank you so much!

        1. Hello Andres!
          To sort the result of a multi-select drop down list, you can add this code:

          Dim words() As String
          Dim sortedText As String
          Dim i1 As Integer, j1 As Integer
          Dim temp As String

          ' Split the text into words
          words = Split(Destination.Value, DelimiterType)

          ' Sorting words
          For i1 = LBound(words) To UBound(words) - 1
          For j1 = i1 + 1 To UBound(words)
          If words(i1) > words(j1) Then
          temp = words(i1)
          words(i1) = words(j1)
          words(j1) = temp
          End If
          Next j1
          Next i1

          ' Combining sorted words into a string
          sortedText = Join(words, DelimiterType)

          ' Write the sorted text back to the destination cell
          Destination.Value = sortedText

          Insert this code before the lines of code

          End If
          End If
          exitError:

          1. This works perfectly! Is there a way to apply a custom sorting rule instead of in alphabetical order?

            Say I wanted a list of names like Tim, Bob, Bill, Chelsea, Kora, and Xander. But I wanted Xander to always appear first if selected, then Kora, then Tim, Chelsea, and so on.

            Is there code I could insert to sort the selected data from a custom ordered list (maybe a column on another hidden row or sheet?)

            Thanks!

            1. Hi! Your request goes beyond the advice we provide on this blog. You can try to do it yourself using VBA.

  29. Thank you for this! This is exactly what I needed and explained greatly, but a question:
    I now have the code so that the choices are seperated by ", " & vbCrLf So that it puts the words under each other in the table. But when I wanted to use the filter in the table it put all the words in the cell together. Is there a way to change the code so it reads every choice as a separate? Or maybe a different macro somewhere? I couldn't find it.
    Like a code that says that words in a certain colom are divided by a "," and the filter has to read the words separated by "," as different words. Even if they are in the same cell. So the filter shows all the cells (and rows in the table) that contain the 1 word I am filtering on.

    It seems do-able but I don't have the skill and knowledge yet by far to figure it out.

  30. Just like the other comments it works initially and then refuses to work after I save and close (opened the next day and multi select doesn't work) I've looked into the trust center and still nothing. Thoughts?

  31. Hi Alexander - This works great! Where multiple selections are set to go to the next line - is it also possible to add a custom symbol (ie. ;) after each response as well ... combining the new line option with the custom delimiter?

    1. Hi! If I understand you correctly, you can use multiple characters in the delimiter. For example:

      DelimiterType = ";" & vbCrLf

  32. It was successful at first. However, when I closed the file and reopened, the multiple selection no longer worked. When I tried to view the VBA code in the Code Window, I could not even open the Code Window. I am sure about saving it correctly as .xlsm file.

  33. Can checkbox come in the drop down list? it will be helpful

      1. I think it is possible - by combining data validation with ListBox (ActiveX) or ComboBox (ActiveX).

  34. Hey thanks for the code, its indeed helpful. But i just noticed that the options in target cells dont automatically appear as a dropdown list. Instead i am having to key in a letter and then the list displays all contents of the source list that contains the key word i typed. How can i have a drop down list with all items from the source list displayed for me to choose from?

    Many thanks in advance for the support. Much appreciated.

  35. This article has been a lifesaver! I do have a question about how I can have the macro applied to all worksheets in my workbook without having to manually add the macro each time I create a new worksheet? I have to make a new worksheet monthly and it would save a bit of time if I could have it apply automatically. Thanks

    1. Hello Brandy!
      The Private Sub Worksheet_Change event works only on one worksheet where the VBA code is written.

  36. does it work when the dropdown table/list is in a separate spreadsheet?

      1. Hello Alex,
        Thanks for getting back to me. is there any code that i can use to run Macro automatically when i open excel workbook? Thanks

      2. Thank you, I tried this code & it was brilliant. Since next day the code is not working Not sure why?

  37. Is there a way to add "and" before the last selection? ex: instead of "test 1, test 2, test 3" I'd like it to display "test 1, test 2, and test 3". Thanks!

  38. I am able to get the multi select to work for myself without any issue but when I share the workbook with colleagues, the multi select does not work and the information is simply replaced when the next item is selected.

  39. Hello! First let me say, thank you! These are very niche problems and I am grateful that someone has created solutions for them.

    I am having a couple of issues. First issue is that, with the code that includes removal functionality, the removal piece doesn't work for all items in the dropdown list. For the first few it works, but for item #4 and up, a second click just duplicates the selection. Can you help with this?

    Second thing is less of an issue and more of a request pertaining to the first issue -- is there a way to add the 'No Duplicates' bit of the code into the code for removal capabilities?

    Thanks in advance!!

    1. Hello Will!
      I have not been able to reproduce the first problem you write about. I can delete any number of items correctly. Check to see if some items in your list use characters that are also used as list delimiters. This is discussed in detail in the article above and in the comments.
      As for the second problem, we don't modify VBA code at the request of users. You can try to do this yourself.

    2. I will just clarify -- I would like to have removal capability AND to block duplicate selections for all dropdown items

  40. This is awesome thank you! Is there a way that you can create a counter for this? Using the example listed above, let's say all four rows need "Salt", am I able to create a counter table that shows how often the word "salt" is said in each of the four rows? I tried using a pivot table and it counts based on what each individual cell says, unable to separate the individual words

  41. Hi when I try to apply the code for a protected worksheet after the main code, the functionality of the multi-select is either taken away or there is an error - could you please provide some guidance other than the instruction above of exactly where the code needs to be added after the main code, and also if the sheet needs to be password protected firs before entering the VBA code for protected worksheets?

    1. Hello Adrie!
      In addition to the above instructions, the usage of VBA code for multiple selection on a protected sheet has been discussed in detail in the comments below.

  42. Please develop a solution THAT SUPPORTS MULTI-SELECT and offer it as part of your add-in - you'll sell loads!

  43. Hi Are we able to do multiple selection and also have dependent drop down in the next column please.

    For eg if I select milk and apple in the next column get the dependent dropdown semi/ no fat/ full fat as well as drop downs for apple

    1. Hello Nancy!
      Create a dependent dropdown list as described in this guide: How to make a dependent (cascading) drop-down list in Excel. With the help of the macro proposed in this article, you can organize multiple selection in any drop-down list, including the dependent drop-down list.
      You can only make multiple selections in the dependent drop-down list. If you select 2 values in the main drop-down list, the dependent drop-down list will not work.

  44. Hi, this is superb! Can you multi select from the dropdown. I have a need to select about 20 items from the drop down and I am having to click on dropdwon arrow and select one at a time.

  45. In regards to your note under your code "bear in mind that the code will only function correctly for single-word items, failing to handle multi-word items containing spaces" by any chance do you have a code for multi worded items containing spaces?

  46. This was just what I was looking for! One question, I have removed any error message from my validation lists but still get a Data Validation error when I select multiple options in the list on my table. Any advice?

    1. Hello Sydney!
      Unfortunately, I can't give advice as I can't see your data and can't guess what error message you removed. Try using the sample file linked at the end of the article.

  47. So very helpful! Thank you so much!

  48. This is so helpful, thankyou!

  49. Hello!
    I used the "Item removal" example and changed the delimitertype to "* " & vbCrLf so that the items look more like a summary.
    But the asterisk ain't showing for the first item selected from the list. Can you point me in the direction where to change this in the code?

  50. Thanks a lot. A little issue: I'm still getting a Data Validation Error when I multiselect. Is there a way I could send over my Worksheet so you look over?

    Regards

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)