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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. So very helpful! Thank you so much!

  32. This is so helpful, thankyou!

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

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

  35. Hi!

    This worked exactly as described, used the option to be able to remove selections.

    ??? Question ???
    Any tips on adding in a sort to the back side of this so it always either Alphabetizes the list or orders it in the order of the drop down source list?

    1. Hi! A drop-down list creates a text string in a cell. To sort this text string, you need a special macro.

  36. Good day
    I have a task and it wants me to paste a table from one file into another separate excel file by using a dialogue box with macros by getting the code from excel .However the code keeps giving the error "debug".Could you please help me .

  37. Hi, great explanation. However, my choices in the drop-down list are not separated by a comma, and I cannot figure out why as I have followed all the steps in your guide (I think).

  38. I don't understand where to put the code for the protected worksheet. When I add it nothing else works or the multi-drop down doesn't work. Can you please tell me where to add it in with the Multi-selection dropdown with item removal.
    Thanks

    1. Hi! All the necessary steps on how to insert code into a protected Excel worksheet are described in detail above.

  39. Hello! Have you ever tried to create a slicer of the drop down data? Does it work? If so, how? If not, then is there a way to do it?

  40. I am not able to apply the code suggested to make the multi selection functionality work when a sheet is protected. If I cut and paste the code at the bottom of the code I copied from the multi-selection without duplicates option and try to go to use my drop down list, I receive the following error: Compile error. Ambiguous name detected: Worksheet_SelectionChange

    2. I forgot to mention that I added the specific cell reference code to the original code because I have multiple data validation lists in a sheet and only wanted the multi selection applied to one of them.

  41. The codes are great, however the multi selection option didn't work when you protect the sheet and allow multi selection cells for editing.
    Any help on that?

    1. Hi! I can't check your worksheet and I can't check your VBA code. However, this is a question that has been asked many times in the past. You may be able to find the answer in the comments section below.

  42. This code is great! I'm wondering if there's a way to provide a default text display that goes away when something on the list is selected.

  43. This isexactly what I was looking for
    And was super easy to do
    thank you for that
    I love you

  44. How would I get the vba to look at column header instead of number? I have a column called "Area of Impact" and if I add or move columns, the VBA wont stay on that column.
    Thanks for the code by the way, exactly what I was trying to do.

    1. Hi! I'm really sorry, we cannot help you with this. We do not do VBA code creation or customization on request.

  45. Thanks for this. It worked great. However, when i shared the file it no longer worked. But it still works on the original file. Any thoughts? I am trying to share a Onedrive link for others to collaborate on the same file.

  46. This worked (it took me a minute because I put the code on the sheet that had the list values first instead of the sheet that I was putting the selections on - I left the code on both sheets and there's no problem, so that's nice too!).

    I am super excited about the multi-select - with removal! - because I can have one column instead of 2 (one to say multiple items and a second to list out multiple items). This will make for a smaller, less intimidating spreadsheet for the people who have to fill it out - and it cuts down on entry errors. Win win all around!

    Thank you!!

  47. This is really clever and useful work! Thank you so much for sharing. I've used the code for multiple selection with item removal, and it works just fine. However, as soon as I select more than one item to appear in the cell, there's a cell data warning triangle sign, telling me this: "The value in the cell is invalid or missing. Click on 'Display Type Information' for specific details.", and then, this Field Type Information says this: "Data Type: List. Restriction: Value must match one of the listed items." Do you happen to know what this is about?

    1. Hello Constantine!
      Unfortunately, I was not able to reproduce your problem. VBA code to select multiple items in dropdown list works for me without errors. Note that the same character cannot be used as a delimiter and part of a drop-down list at the same time. That warning is in the article above.
      If this does not help, explain the problem in more detail. Give an example of the data you use in the drop-down list, what list separator you use.

      1. Thank you for your response, Alexander! On my end I'll play a bit more with the scripts and data in a new spreadsheet, sometime next week, to see if I can get a better feel for the whole idea. I think I need to redo everything from scratch, to avoid any errors. I'll get back to you if/when I have clearer questions.

  48. I'm not sure this can be done but, I'm trying to create a multiple entry dropdown box, but for each entry I select I want to be able to select specific information from another multiple entry dropdown box. For example, I am trying to identify a list of claims in the first cell, but as I enter each selection, I want to be able to "attach" multiple bases to that claim from the cell next it.

    Like Cell D2 my first selection is CONSTRUCTIVE DISCHARGE. When that selection is entered, I want to be able to select from Cell E2 bases like AGE, DISABILITY and NATIONAL ORIGIN.
    Then, I would like to make a second entry in Cell D2 like DISCIPLINARY ACTION. And when that is entered I need to select bases from E2 like: DISABILITY and RACE

    Right now, I have to list one Claim in D2 then add the Bases in E2, then drop down to D3 and enter another Claim and then enter the Bases for that claim into E3. That requires me to merge all of the other cells once I am finished entering the Claims and Bases, so that each claim and corresponding bases are captured under the same case number. But that's a pain because once I start merging cells, I can't sort the entire worksheet.

    It's probably impossible to do. Or maybe I need to use a combination of dropdown lists and picklists. I don't know. Any advice would be appreciated.

    Thanks

    1. Thank you, Alex. I'll look at the Dynamic Dependent Dropdown Box link and I will probably have to go out and get a VBA code for Dummies book so I can learn ho to do this. It's kind of funny that I'm going through all of this time and effort to create a function on my spreadsheet that is designed to keep me from expending excess time and effort when entering data. But, I'm stubborn that way and it will be worth it in the end.

      Thanks again for your quick response and assistance.

  49. Thanks for this detailed explanation. I was wondering if it would be possible to "connect" a selection to another cell - here are the details:
    I want to have a multiple choice dropdown in column D (this works) and a multiple choice dropdown in column E (works as well). Now, if column D indicates the profession (e.g. doctor), I want to have a multiple choice dropdown in column E (indicating area of expertise) that just shows me the choices of areas a doctor might have expertise in (e.g. immunology, cancer).
    Does this seem feasible? If yes, how would one accomplish this?
    Thanks in advance.

  50. Hello,

    I am by no means tech savvy, but your instructions were clear, and I was able to create a spreadsheet with multiple drop downs and I am very much appreciative of you. I would like to be able to change text in a cell after selecting a drop down to customize it. For example, my drop downs include:

    Background, pg.
    Policy, pg.
    Policy, Section, pg.

    I would like to be able to change text within a cell and not necessarily within the table for example

    A4. Background, pg. 1
    A5. Policy, pg. 6
    A6 Policy, Section 1a, pg. 6

    Currently, when I change the text, or strikethrough something (to keep history) i.e., Background, pg. to Background, pg. 1 they will both appear in the drop down. When I try to delete one, Background, pg. 1 will appear 2x along with Background, pg. Also, if I try to strikethrough an item on the list it will duplicate the whole list.

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