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 3. Total comments: 658

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

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

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

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

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

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

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

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

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

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

  11. So very helpful! Thank you so much!

  12. This is so helpful, thankyou!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi,

    I have inserted the code and it works perfectly fine for me and for several other colleagues but for some of them it doesn't. The file is stored in a Teams folder but always opened in the desktop app. Any idea on what the reason for this could be? Could this be settings related on their end? Pleased to learn and thanks in advance!

    Toon

  32. Hello!

    Great code! I was wondering if there's a way to replace values in the dropdown selection after selections have been made. For example, if I have in a cell: "Bread crumbs, Celery, Onion," I want to change "Bread crumbs" to "Crumbs" in the source sheet and see the same change reflected in the dropdown without having to clear the dropdown and reselect all values again. Is there a way to do this?

    Regards - Otto

    1. Hi! A drop-down list creates a text string that cannot dynamically change depending on the values of other cells.

  33. Hello! When attempting to use this code I have been getting "Run Time Error '50290': Method 'EnableEvents' of object '_Application' failed" and then when I click debug it highlights the "exitError: Application.EnableEvents = True" line. What would be the solve here? I copy and pasted the code exactly into VBA, so I can't figure out where it's going wrong.

    1. Hi! Try to insert the code correctly again. You can also insert the code from the sample file linked at the end of the article.

  34. Hi! This works great - but not in the web version of excel in office 365 / sharepoint. Is there a way to enable this for web based excel?

  35. I have two columns that have are using a drop down list and the code is working on one of the columns but not on the other. Is there something I am missing? My understanding is the code should work on any lists on the sheet?

    1. Hi! I don't know what code you have installed. The article above suggests codes that work on entire worksheet, but can also work in individual columns, rows and cells: Multi-select dropdown for specific columns, rows, cells and ranges.

  36. I can't get the code to work (multi select with delete) when I have the worksheet protected. I added the password sub code you provided to the Sheet2 code (pasted between Option Explicit and Private Sub Worksheet_Change(ByVal Destination As Range)). Is there anything I need to add to UserInterfaceOnly to get this to work?

    1. Hi! It is enough to follow all the instructions given in the article above. Or use the sample file from the link at the end of the article.

      1. Hmm. I have tried it several times and can't get the code to work, even on the sample file. I can only ever select one item from the drop down when the form is protected. Are there any other trouble shooting areas I can look to?

        1. Hi! Unfortunately, I can't check the code in your workbook. All necessary instructions are in the article above.

  37. Hello, thanks for this detailed explanation. It is very helpful. I am using the VBA code to select multiple items in dropdown list. I was wondering if there is a way to count how many times each word/slection is used. I tried using the =COUNTIF but as soon as a word/slection is repeaded from the dropdown list, it brings the count to 0.

  38. Hello, thantks for the content, is very well written and the code works perfectly.
    I have one question, is it possible to filter those informations inividually? Because by using your code I've noticed that the informations are classified as one new variable, but I would like to obtain a filter in the colum that would get for me all the lines that have "eggs" for example. How can I do it?

    Thanks

      1. Hi Alexander,

        Thanks a lot for this helpful comment. Can you please expand on what you mean by partial match? I checked the article suggested and couldn't find the partial match you suggested.

        Thanks
        Oudai

  39. Hello! This isn't working for me... Does the data validation have to be in the same sheet as the drop down menu? I used the additional code: "If Not Destination.Column = 4 Then GoTo exitError" in the correct sheet and column but it won't apply to the drop down menu.

    1. Hello! Unfortunately, I have no way of knowing what you did or how you did the code installation. The code should be on the same sheet as the drop-down list. Follow the instructions carefully, or you can download the sample file at the end of the article.

  40. Sorry. I have one more question. Can I use two different codes on the same sheet (i.e., multiple selection dropdown with removal for cells C:10:D50 AND a multiple selection dropdown with repetition for cells H10:H50). I can get the first own working, but when I drop the code for the second one, nothing works. I know it's in the code but I just don't know how to fix it. Thanks

  41. I'm sorry, but I have another question. How can I combine 2 of these VBA codes together so that I can have a certain set of cells (i.e., D3:F15) as multiple selection with removal and then another set of (H3:H55) as multiple selection without the removal function?

      1. That is unfortunate. But at least now I know it's not something I was doing wrong that kept it from doing what I wanted it to do. Thanks again for these codes. With the exception of the extra things I want, they do exactly what I need.

  42. This is great. Is it possible to number each item in a multiple dropdown list AND be able to preserve that number if you accidentally selected an item and then had to remove it?

      1. Fair enough. At least your response gives me hope that it can be done. Going to the bookstore now to get Visual Basic for Dummies.

  43. Awesome, many thanks for teaching a very old dog new tricks!

  44. THANK YOU! This has massively helped me in what I was trying to achieve. Really clear instructions that actually work. I'm not going to pretend to understand it all but I don't need to!
    Thanks again.

  45. Hi - I am using the code for 'Multi-selection dropdown with item removal' and its working correctly across the whole sheet. I am trying to narrow it down to only a few columns. I have tried to insert the code "If Destination.Column 4 And Destination.Column 6 Then GoTo exitError" and it has not worked.

    Is there a specific spot to insert this code? The instructions are not clear to me where it should be entered. "Add immediately after it, add a new line specifying where to allow multiple selections, as explained in the below examples."

    Thanks for your help!

    1. please ignore me - worked it out.. my mistake!

  46. Hi,

    Great article!

    Id like another cell to tell me the number of selected items. How do you count the number of selected items?

  47. Hello! Your instructions worked perfectly! My question is that I need to save to OneDrive so others can edit and update but I know that I can't do that with this file because it is saved as an xlsm file. Any way around this? Thank you!

    1. Hi! In OneDrive, you can save any files you want. But, as has been said many times in the comments to this article, the macro does not work in Excel for Web.

  48. Hi, I have 2 columns with dropdowns, Colomn 1 (a,b,c,d) and column 2 (a:123, a:456,a:789, b:123, b:456 and so forth). If I select a in column 1, I only want the data with a in column 2 to appear. How do I do this please?

      1. Thank you, I will have a look.

  49. Hi,

    I've been playing around with these macro's and everything works great! Thanks a lot for the clear instructions.

    Unfortunately, sometimes suddenly the scroll bar in the selection menu seems to stop working, and then randomly starts working again. I was wondering if anyone else is having this issue and whether there is a known cause (and maybe even a fix, but I'll settle for a cause :-D).

    I should note that I'm on the semi-annual enterprise edition of Excel, so I unfortunately don't have the most recent features that are released (including searching through the dropdown menu, which hopefully will come soon).

  50. Hi! I am trying to create a worksheet that will populate a weekly meeting activity when you select a "week of" date from a drop-down box. Is there a way to do this?

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