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 4. Total comments: 552

  1. Hi
    Thanks really finding your site useful.
    Have been quite successful with your guidance, so to take it further
    , how can I get my drop down boxes to reset when I want to put a new entry in from the start, ie enter a name drop down gives age and favorite colour. then enter a new name and boxes return to new entries not the previous ones.

    Hope this makes sense
    Thank you

    1. Hi! If I understand correctly, you can clear a cell with a drop-down list just like any other cell - by using the DEL key.

  2. Hi! Really appreciate the article and code. Thanks!

    The task is to create a product list with fixed prices, for which you can add optional accessories at additional cost. One column for 'Product' , one for 'Price' and one for 'Accessories'.
    What I want to do, is to make the accessories column a drop-down list (sourced from a master accessories and prices table) where I can add whichever accessories I wish, and the total price automatically updates depending on the attachments selected. Should also support accessory/item removal in case of mistakes.

    Thanks in advance!

  3. Hi there, is it possible to have the final result of the multiple selection arranged in alphabetical order?

    1. Hi! See the comments below. The answer has already been given. You will need to use a separate macro to sort the text string.

  4. Hi,
    Thanks for this post, was really helpful and clear.
    I used it for a file I created and used a drop down list with multiple selection, and I did the change in the code so it will be presented one below the other and not separated with ","

    But, for some reason, sometimes some of the cells with the data gets tempered and it jumps and being separated by space.
    example:
    1. when it's correct:
    Kitchen
    Bathroom
    Room

    2. When it's changed (I don't know what causes this or how to even try to fix it):
    Kitchen Bathroom Room

    I tried using alt+Enter, it shows an error: "this value doesn't match the data validation restrictions defined for this cell."
    another thing is that this is a multiple selection WITH option to remove. but when it gets tempered - selecting for removal doesn't work - it adds it again.

    Hope I explained this clearly and that someone can help me with it.

    Thanks,
    Sheena

    1. My mistake, when it's tempered, it just connects all the data: KitchenBathroomRoom like this.

      1. Hi! If my understanding is correct, you can see the working code on the "Selections in Separate Rows" worksheet in the sample file that is linked to at the end of this article. Hope you’ll find this information helpful.

        1. I downloaded the worksheet, and it doesn't allow multiple choice when I try to do it myself.
          In my excel file, it does allow me to select multiple lines from the drop down and it indeed presents it to me one under the other.

          But not sure why it sometimes gets tempered and gets out of order.

            1. Hi again, to clarify, I have no spaces in any of my drop down items, they are all single word items without a comma or space. I've made a brand new workbook to test it with items "test", "test2", "test3", and "test4"

              There are no delimiters in any of the drop-down items. When I have 3 options selected and remove 1 of them, by manually pressing backspace on the text, it comes with a validation error. When I turn off the error notification, it brings back the removed item and duplicates the text that was remaining, so "test, test2, test3" becomes "test, test2, test3, test, test2" when I try to remove "test3" then becomes "testtest2test3testtest2" when I select the text and press enter

              I am using the "Multi-selection dropdown with item removal" code

              1. If you want to manually adjust the data in a cell, why do you need a dropdown list? Try doing it in a normal dropdown without multiple selection and you will get an error too. If you use the "Multi-selection dropdown with item removal" code, you can't get a text string with duplicates of "test, test2, test3, test, test, test2".

            2. Hi, I also have their problem, and my macro is not blocked. The code works except from when I manually edit or enter the text without using the drop down menu by either using F2 and pressing enter or by clicking the formula bar and pressing enter. As long as there is more than one option selected when I press enter it will either think I've made a data validation error or if will remove all delimiters and continue to generate duplicates of the text everytime I try to remove the error unless I delete all the text in the cell.

              so "kitchen, bathroom, Room" will turn into "kitchenbathroomRoom" then when I try to remove the "Room" it then becomes "kitchenbathroomRoom, kitchenbathroom" or if I try to remove "Room" from "kitchen, bathroom, Room" it becomes "kitchen, bathroom, Room, kitchen, bathroom"

              1. Hi! Your problem has been discussed many times in the comments. The solution to this issue is also described in the article at the end of the second section. Please read it carefully. The same character cannot be a delimiter and part of the dropdown items. In our code, the default delimiter is a comma followed by a space character (", "), so this combination of characters should not be used anywhere in the dropdown list items themselves to avoid conflicts.

  5. Wow, this does exactly what I wanted it to!
    One question regarding putting a filter over columns with multiple items ticked. Say I have 2 different rows:
    eggs
    eggs, milk

    When I filter this data I wish to just press on "eggs" and have them both be live, rather than displaying as two different options in the filter.

    Is this possible? It is my first time using VBA code!

    1. Hi so I created a multi cell drop down list but I need to do more.

      1. In a different cell I need to show the items which was not selected in a my multi drop down list cell.
      2. I need to sum up my chosen options from my multi drop down list and my items not elected

      1. Hi! An Excel drop-down list with multiple selections creates a string of text in a cell that contains all the items that were selected. To do what you want, each item must be written in a separate cell.

  6. Appreciate you posting this. Very helpful information and you helped me do exactly what I was looking to do. Essentially I was looking for Excel to mimic a function that MS Project does, which is assign resources to specific task items. I created a list of "Leads" or techs. Then applying you code. I utilized the code that included removal. I also added the column destination line as well as changing the comma delimiter with the drop line vbCtLf. It all works great. The one issue I have is, that when more than one name is selected, i get a data verification error. Doesn't matter if I am using the original comma delimiter or drop line. Data verification sees multiple names and marks it.

    It's a minor issue and if the answer is no, it can't be fixed, I am fine with that. But because I'd prefer the cleanest code possible, I would like to ask. Is there a way I can correct the process above to allow me to select multiple names and not trigger a data verification error. That is without turning off data verification off completely. Even if I just got it to ignore errors for this one column of one workbook, that could work.

    Aside from this minor issue, your code is great. Thank you for posting and sharing.

    1. Hi! I don't know what changes you made to the code or what data you used for the dropdown list. Try downloading the sample file from the link at the end of this article and pay attention to the worksheet "Selections in separate lines". The code works without errors there.
      Also make sure that the delimiter is not used in the list of values for the dropdown list.

  7. Can this be done with Excel Online which does not allow you access VBA?

    2. I am trying to figure it out the same thing :( For me it works in the Excel App but when we open the spreadsheet in the Browser, it does not work.

  8. Hi!

    I have a multi-select dropdown list with the ability to remove using your VBA code. Is there additional code available to automatically sort the selected list items alphabetically when they are selected instead of placing them in the order they were selected. For instance if I select items 1, 3, 5 but in the order of 5, 3, 1, they are listed as 5, 3, 1. I would like them to list as 1, 3, 5 despite the order I select them in.

    Thanks!

    1. Hi! This question has been asked before, and the answer was "no". The drop-down list creates a text string. You can try to sort it using an additional VBA macro.

  9. I have a worksheet that requires 4 different multi drop down lists for 4 different columns. I have tried to replicate what i did for the first one but it does not like me changing the formula ie =INDIRECT("Table1[Items]") to =INDIRECT("Table2[Service]"). Can you only have one multi drop list?

  10. Hi, thank you for sharing the codes. In situations when you have a large list, is there a way that we can type the text and it automatically select the selection in the list? instead of having to go through the whole list to find that one selection.

    2. And how can I use pivot table to organize all the entries without duplications.

      1. Hi! The pivot table does not use the standard dropdown list. The code works only with the dropdown list.

  11. Hi! I need to do the multiple drop down in multiple columns. I have tried adding another range line
    If Intersect(ActiveCell, Range("D4:D79")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
    'do nothing
    or adding another section of code (copying the code again but changing the range it affects) but this does not work. Is there a way to do more than one in an excel sheet?

    1. Hi! Use the Union function to check whether the active cell is in one of the ranges.
      For example:

      If Intersect(ActiveCell, Union(Range("C3:D10"), Range("C15:D17"))) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then

      I hope it’ll be helpful.

      1. Thank you! This solved the issue

  12. Is there a way to add the code over every sheet in a workbook without having to do each sheet individually, so as you add sheets, they already have the code?

  13. Codes are working fine but for protected sheet I am able to make a single entry only. Multiple entry is not possible with it when sheets are protected. I have applied your code for password protected sheet but it is not working.

  14. The multiselect with removal does not remove the first entry. It will remove all other entries but does not return to blank.

  15. Hello, Is there a way to ensure your drop down list is always in alphabetical order and not click order?

    1. I sort the source to be A-Z, then drop down list appear in A-Z order as well.

    2. Hi! A drop-down list creates a text string in a cell. You need a special macro to sort the words in this text string.

  16. How do I make a field on the drop down where they can enter text as and "other" option?

  17. I'm trying to use your code to enable multi-selection functionality in protected sheet, but I don't know where to put this code in the VBA (I'm also using your code for "Multi-selection dropdown with item removal") Can you please provide me with the exact location where this code should be placed?

    1. Hi! This has been discussed many times in the comments of this article. Add the code after the "Multiple selection drop down with item removal" code or whatever code you use.

      1. Hi! There are no errors in the code. But I can't look in your workbook and your Excel to find the cause of the problem. Try installing the code in a new workbook or downloading the sample file from the link at the end of the article.

        1. I created a brand new file and copied the Multi-selection dropdown with item removal code and added If Not Destination.Column = 4 Then GoTo exitError before the "TargetType = 0" line. This worked as expected. I added the How to enable multi-selection functionality in protected sheet code at the end. The new file has "password" as the password. When I click on the drop down, the file changes to the sheet with the Table for the drop down and changes the Table to a list, not adding the second selection to the drop down.

          1. Hi! Unfortunately, I did not have a replication of your problem in my workbook. Please note that the code to enable the multi-select functionality in the protected sheet should be added at the end of your code, after the main code.

  18. Is there any way to apply multiple dropdown feature on web excel ? Like on "OneDrive"

  19. An honestly wonderful bit of code you put together. I am curious if it is possible to limit the selection to a particular range? I have tried adding each cell of the range, but the list gets excessively long (B11:C50). Neither column nor row limitation work perfectly as it negatively impacts other parts of the worksheet.

    Hopefully I am overlooking something simple. Regardless, thanks for your VBA (Very Beautiful Algorithm).

    1. Hello! Replace the following line of code to restrict the execution of the multiple selections macro to a range of cells

      If Intersect(Destination, rngDropdown) Is Nothing Then
      на
      If Intersect(ActiveCell, Range("B11:C50")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then

      I hope my advice will help you solve your task.

  20. Hi, this is great.

    I used your code for Multi-selection dropdown with item removal for a specific cell option and got it working as I wanted. However, for some reason, it broke some of the macros I had in that worksheet. All the sudden my macros started giving me "variable not defined error" in multiple instances. Deleting your code fixed the issue.

    I have your code at the very top in Sheet1 followed by over 20 macros triggered by buttons

    After spending some time digging for answers the problem relies in the fact that your code requires the "option explicit" line. This forces me to define every single variable in my macros.

    is there any workaround other than defining every single variable in my >20 macros?

    Any suggestion is welcome.
    Thanks

    1. Hi! If the "option explicit" option appears in the module, you must explicitly declare all variables using Dim, Private, Public, ReDim, or Static instructions. You can try removing this option, but I don't guarantee the code will work correctly.

    2. Ok found a workaround (i think). i have to cut and paste the macros from the buttons into separate modules and then assign those modules back to the buttons.

      Cheers

  21. Is there a way to show the dropdown symbol in excel cell using VBA (for multi selection dropdown handled thru VBA code)?

    1. Hi! Excel has a special method of identification for cells with a drop-down list. The code for multiple selections works in all cells with a drop-down list. Unless you specify a specific range. You could try writing VBA code to get what you want, but we don't provide that help on this blog.

  22. Thanks for the instructions! Now I want to send this Excel file to colleagues. Tried it, and from some email addresses it bounced back. I am presuming because of the macros in the Excel file which could be seen as a security risk. I tried creating a xlsx, but it loses the multiselection functionality. Is there any way to preserve the multiselection functionality in a document format that won't be perceived as a security risk?

  23. Thanks for this tutorial. It was very helpful.
    I have created a pivot a table and I will like my multi-selections to appear individually as column headers. Can this be achieved?

  24. I simply cannot discern where, within the code that appears in the "VBA code to select multiple items in dropdown list" table, that the suggested code for introducing column flexibility (below) should be inserted! Please advise. Thanks.

    Multi-select drop-down for specific columns
    To allow selecting multiple items in a certain column, add this code:

    If Not Destination.Column = 4 Then GoTo exitError

    1. Hi! Pay attention above to the beginning of paragraph: How to create multi-select dropdown for specific columns, rows, cells. It says where to insert this code.

  25. Hello, thank you for this useful website.

    I have questions please,
    I tried making a drop down with multiple selections for my data, but when I insert the formula =INDIRECT("Table1[Items]")
    I get an error message saying (the source currently evaluate to error !)
    I don't why?

    Another question: I cannot see from where the name "Table1" in the above formula came?

    Thank you

  26. I am not an expert in excel. Your VBA code helped me a lot. But still am facing problem with the "protection".

    Can u explain where to insert the protection code along with the existing code.

    I did copy, paste with the existing code, but it doesn't work. do you have some pictures or videos to share.

    Thank you.

    1. Hi! You can see a sample code in this paragraph above: How to enable multi-selection functionality in protected sheet.

      1. Hi,

        I've tried to the copy paste of the code that was recommended in the paragraph above, but it didn't work. I saw other people had the some issue. Could you please give an example of the complete code including both the Multi Selection functionality as well as the code to Unprotect and protect so we could evaluate what we're not doing properly?
        Or even include this type of example in the excel spreadsheet you provided as an example?

        Thank you in advance

        1. Hi!
          1. Add the code you need to the worksheet. For example, Multi-select drop down without duplicates.
          2. AFTER THIS CODE, add the code for working on the protected sheet from the paragraph: Enable multi-selection functionality in protected sheet.
          3. That's all.

  27. The multi-select code with and without removal does not work. I have read and implemented the instructions 6 times and only one item can be selected. I have saved the sheet as a macro enabled excel file. I have created the list using the table and the "INDIRECT" line of code. I have created the developer tab and added the VBA code exactly as it is shown in the article. But only one item can be selected.

    1. You may want to add a step to your list. I had to close out of the spreadsheet entirely and re-open it for the macro to be enabled.

      This code is a tremendous help. Thanks!

  28. Hello,

    Thanks so much for this code - it's great! One challenge I have though is that when you use the code to enable removal of previously selected items it doesn't quite work for me. Let's say I have 2 items that can be selected - QWE; RTY and am using the custom delimiter: " | "

    If I select QWE, then RTY, they are now both added to the list like this : "QWE | RTY"
    If I re-select RTY, it is now removed from the list like this: "QWE"
    If I re-select QWE a second time, it is added to the list a second time, NOT removed, like this "QWE | QWE"
    If I re-select QWE again a third time, they are both now removed and there is nothing left in the list.

    The case where an item is added twice only happens when you try and remove the first item in the list. My best guess is that this because the first instance automatically doesn't have a de-limiter, so perhaps the code for removal is looking for a de-limiter, doesn't see it, then s then adds the item again with one? Then when you select a third time, it picks them both up due to the de-limiter?

    I'm not sure, I'm not great with code but would appreciate if you're able to provide any other advice on this. this is the only VB I have in this document.

    Thanks!

    1. Hi! We have made changes in the code. Reinstall the code Multi-selection dropdown with item removal. Thank you for your comment!

  29. Hi,
    code was working greatly, but I have now new company notebook, suddenly code stopped working. I have downloaded your practice sheet, and it does not work as well.
    No errors, dropdown list allows only one selection.
    Excel version: Microsoft® Excel® for Microsoft 365 MSO (Version 2310 Build 16.0.16924.20054) 64-bit
    Any suggestions ?

      1. Hi.
        I understand the basics for macros to run.
        OFF COURSE all macros are enabled, folders trusted.

        Anyway it doesnt work. I was trying altering the code.
        I deleted this section at the end and it worked for a while. Then again stopped working.
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        End Sub

        Looks to me like option explicit doesnt run properly.

        1. Hi. On the old computer the code worked, on the new computer it does not work. Conclusion: your new computer software is the problem, not the macro code. Unfortunately, I am not able to solve this problem for you.

  30. Hi Thank you for this code - it works but I wanted to take it a step further. I am using the code which places the selected items on a new line (so DelimiterType = vbCrLf), but I also want to add a comma to the previous line.

    I have tried "DelimiterType = & ", " vbCrLf" - but this creates an issue because it allows an item to be selected twice, then after selecting that item a couple more times, only then do all instances get deleted.

    Can you advise how to add a comma to the end of the previous line?

    Thank you.

    Gemma

    2. Actually, I've just recopied the code and used "DelimiterType = "," & vbCrLf" and it seems to be working now.

      Thank you

      Gemma

  31. Hi, thank you this code was very helpful. I am using the code which allows duplicates to be selected and I would like to count how many times each word is mentioned (including duplicates within cells). Do you know if there is a way to do this? I have tried the COUNTIF and SUMPRODUCT functions but cannot find a way to count the duplicates also - it counts a maximum of 1 per cell. Thanks.

  32. Hi. Thanks for the comprehensive explanation. I have used the VBA successfully and created the multi selection list for all my worksheets - it works great! Once i have collected my data, is there any way to calculate/summarise how many of each selection within a drop down list have been selected. i.e. if i and counting fruit and have selected "apples" ten times (or 1000) and selected "pears" 37 times, how can i get excel to calculate that without me having to count each individual selection.

  33. Hi Alexander I have your code working, but as soon as I close and reopen the file, the code won't work anymore, do you have any suggestions for me please? I check and nothing has changed, I have copied it to a new workbook and the exact same thing happens, it works until I close, then when I reopen, it will only let me select one item per cell. I did not add specified columns to the code, and my worksheet has about 10 list tables, so not sure if it's too much information? I am also very new to VB, so I could be doing something without realising I'm erring. Data tables are on a separate worksheet and the code is only on the worksheet I have the data validation on.

  34. Hi!

    Thanks for sharing this. I tried the code, and it works for examples given above.

    However, I would like to use the multiple selected items as input for a function (e.g. FILTER function).

    Let's say I have a dataset for several years, and want to do a report with the filter function, filtering data for selected years, but giving the user the ability to select one or more years from a dropdown list.

    Is there a way to do this?

    Thank you in advance for your help. :)

    1. Hi! The macro allows you to create a text string of multiple items in a single cell. But I cannot advise you how to use this text in the FILTER function. Each criterion of the FILTER function must be written separately.

  35. Hi - thanks for the code. When I use this I get the multi-select list working fine - however Excel is showing the standard 'data validation' error on the cell, as a concatenated version of selections isn't valid according to the original list defined in the data validation tab.
    Is there any way to get Excel to not show up the issue?
    Further, I cant use the formula bar to edit the result (i.e. manually delete an entry selected), because again, it's not valid based on the original formula list provided.
    Any help would be appreciated!

    1. Also, the password protection seems to be a bit funny! The code seems to turn protection on before I have, and then the list doesn't work anymore?

      1. I fixed the first by protecting the sheet - and the second I used different code.
        Sub Example()
        ActiveSheet.Unprotect
        Program logic...
        ActiveSheet.Protect
        End Sub

        Around the sub I copied. Thanks :)

  36. first of all thank you very much, The code is really good and works great ! ?
    I saw that people wrote that they have a problem when the sheet is protected. I also had a problem exactly like this, that the multipicklist worked fine, but as soon as I make the sheet protected, the multipicklist becomes a normal picklist. And even when I added your code for the protect it didn't help. I investigated your sample file which continues to work fine even when the sheet is protected, And I tried to understand what is the difference between us. I think that I came to a conclusion as to what is causing the problem:
    When a different cell with a different validation is defined in the sheet, it prevents the multipicklist from working when the sheet is protected. I you will add to your sheet another validation in other cell, you will probably see the problem.
    I really don't understand VBA, I would appreciate if you have an idea what can be changed in the code so that it works even when there are several different validations in the sheet.
    Thank you very much !!

    1. Hi! If I understood the problem correctly, you can only use one macro per worksheet. It is written about it in the article. All macro variants are in the article. You can add the code for the protected worksheet to any macro, as described above. I hope I have answered your question.

      1. Thanks for the replay.
        I m using only one macro on the sheet.
        But I have two cells with different validations. Only one of them needs to be a multipicklist and uses your macro, the other validation is a number validation and doesn't have a list at all.
        When I protect the sheet and add your code for protecting, the multipicklist becomes a regular picklist. It doesn't run the code correctly.
        I tried to debug (As I said before, I don't know VBA, but I am programming in other languages). when I select a value from the list, I see that on the line "If rngDropdown Is Nothing Then GoTo exitError " it falls and goes to exitError without doing all the other important lines.
        I would appreciate your help please. Thanks.

        1. Hi! I downloaded the sample file at the end of the article and added the code for the protected sheet. I did not get any errors. Try reinstalling the macro code.

          1. Your code works great, I didn't say it didn't.
            But I meant that if you add another validation in another cell (like a validation on a number) and then you will see the problem.
            In any case, my problem was solved in another way, the client asked to remove his field from the file ?.

            1. As I said, I added another validation in another cell (validation on a number) but I didn't see any problems. There is some problem in your file and maybe reinstalling the code would solve it.

              1. The code works great on protected sheets if i use the codes on your sample sheet, but if I modify the code to make specific columns have the multi-selection option, the code not work and turn to single selection only.
                Please you need to change the code on your sample sheet and add this line code for example (If Destination.Column 16 And Destination.Column 17 And Destination.Column 18 And Destination.Column 19 And Destination.Column 20 And Destination.Column 21 And Destination.Column 22 And Destination.Column 23 And Destination.Column 24 And Destination.Column 29 Then GoTo exitError) to see the problem.

  37. Hi,

    Amazing work. I have successfully implemented the code. However, when I transfer the file through e-mail, and then download it again, the multi-selection dropdown doesn't seem to work any more. Would you happen to know a solution to this problem?

    1. I have resolved this issue, it was due to the excel being a read-only file. I saved it as another file and it worked.
      Thanks again.

  38. Thank you for this code. It has been really helpful. If I want to be able to scroll through the drop down menu rather than using the arrow buttons to go through the list, how would I do that?

  39. This worked perfectly when I first tried it out, many thanks! However, when I got an update of MS Office, the code does not work for 64 bit - any way this could be converted easily?

    Thanks!

      1. I'll try, thanks!

  40. Thank you for your help! I inserted the primary code into my spreadsheet and it worked as described. However, when I tried to add in the additional code to enable a multi-select dropdown functionality in a protected worksheet, I would get the following error: Compile error: Ambiguous name detected: Worksheet_SelectionChange
    It would specifically highlight the first line of the added code which followed the primary code. I tried both protecting the sheet and workbook individually and together, and each time I would get the error. The last group in the primary code has the same "Private Sub Worksheet_SelectionChange (ByVal Target As Range)" language. I tried replacing that with the additional code, but that only resulted in the multi-select dropdown no longer functional. I have updated the password options as instructed.
    I don't see this in the practice workbook, and I'm not sure how to fix. Any help you can provide would be much appreciated. Thanks!

    1. Hi! Check your code carefully. Private Sub Worksheet_Change occurs there twice. Delete the unnecessary code.

      1. Sorry, the primary code I used was for the "Multi-selection dropdown with item removal" option. So that only has one occurrence of "Private Sub Worksheet_Change". The ending version is "Private Sub Worksheet_SelectionChange". I combined this version of the primary code with the "multi-select dropdown functionality in a protected worksheet", leaving 2 occurrences of "Private Sub Worksheet_SelectionChange".

  41. I am trying to create a shared document for work using the Multi-selection dropdown with item removal and it works great however it doesn't cross over to the online version (which all my colleagues would see).

    It still lets me select from the list, the most basic form of it works, but the mutli selection and item removal doesn't seem to be translating over.

    Any idea how I can fix this?

  42. Hi, thanks for the code. Selecting multiple options works smoothly, but if by mistake I select a wrong item in the list, how can I remove it? nothing seems todo the trick. thanks in advance

    1. Hi! To remove the wrong item from a cell, you can use the code: Multi-selection dropdown with item removal. Or you can manually correct the data in the cell because the dropdown list creates a text string. You can edit it in the formula bar.

    2. my bad, apologies, I used the wrong code snippet. all works perfectly

  43. Hello,
    This code works beautifully, until I upload my spreadsheet to onedrive so that I can share it with colleagues, it then loses the option to select multiple options from the drop down list and only allows you to choose one, Is there a way to change the VBA code for a share point excel spreadsheet, Or is there another way to add a multiple selection list for one that opens on chrome ? Any help would be appreciated

    1. Hi! Remember that VBA only works in the desktop version of Excel. VBA macros and functions do not work in Excel Online.

  44. Hello,

    This is all great information!! So far is working perfectly! I do have a question, Is there any code that could eliminate your selection from the drop down box in the next row? For example, If I have multiple people using the excel spreadsheet and we have 2 rows with employee names in the drop down list, 1 person picks 3 names from the list and the next person in the next row needs to see what names they cannot pick.

  45. Thank you very much for providing this code, it works just how it's suppost to! I used the dropdown with multiple selections in separate lines code and realized, that the code does not keep the entries in the order of the drop-down menu. For instance: If you select Apricot and then Bread crumps, it will show it in the right sequence.If you de-select Apricot, it correctly only shows Bread crumps. However if you then select Apricot again, the result will be displayed at Bread crumps, Apricot. Is there a way to keep the selections in the order of the drop-down?

    1. Hello! Since the drop-down list creates a text string in the cell, you need to write special VBA code to sort the words in the text string within the cell.

  46. The drop down list with multi- selection is great.

    Is there any code that could eliminate your selection from the drop down box and repopulate it in the next cell over when it is clicked?

  47. Hello,

    Thanks so much for these codes. It works perfectly. Would it be possible to enhance the code to provide user with long description in the drop down list, but returns the short abbreviation in the worksheet? For example, when entering a state, user types in "A" and the drop down shows "Alaska, Alabama, Arizona, Arkansas". If user selects "Alaska", the worksheet would record "AL".

    Best Regards.

    1. Hi again,

      please ignore. I figured out how to accomplish what I needed. In the line "newValue = Destination.Value", I simply replaced it with "newValue = Left(Destination.Value, InStr(1, Destination.Value, "-") - 1)". Of course, the state list would have to be entered as "AK-Alaska"; "AL-Alabama", ... Drop down list would show "AL-Alabama", but when user selects it, worksheet will show "AL".

      Many many thanks for the codes.

  48. Hi
    Thanks for the code. It works perfectly and it saved a lot of our time.
    Just wondering if it is possible to have different text colour for each phrase in one cell.
    Let say in your example, whenever 'Milk' is selected, it shows with a red text colour and the rest item are shown black. Is this something possible?
    BTW, I already tried the cell rule but it will change the text colour for entire cell not one item.
    Many thanks

  49. First, thank you for sharing this code. I'm excited to see it in use with my current worksheet. However, I have a question first: where do we insert this code? You wrote, "insert the VBA code at the back end of the target worksheet" without details for what that means.

    1. Hi! Read the entire article. Instructions are provided below in this paragraph: Insert VBA code to allow multiple selections.

  50. Hi, thanks for our tutorial!
    May I know if I can filter those "multiple selections" data that we just created?

    1. Hi! The drop-down list with multiple selections creates a text string. To create a filter on a part of this text string, you can try using the advanced filter options as described in these instructions: Filter text data.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)