How to make Excel drop down list with multiple selections

The article shows how to create an Excel drop down that allows users to select multiple items with or without duplicates.

Excel has come a long way since its inception and introduces more and more useful features with each new release. In Excel 365, they've added the ability to search within data validation lists, which is a huge time-saver when working with large sets of data. However, even with this new option, out-of-the-box Excel still only allows selecting 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. With the ability to prevent duplicates and remove incorrect items, this feature can streamline data input and improve accuracy in your Excel spreadsheets.

Excel drop down list with multiple selection

How to make Excel drop down with multiple selections

Creating a multi-select drop down list in Excel is a two-part process:

  1. First, you make a regular data validation list in one or more cells.
  2. And then, insert the VBA code at the back end of the target worksheet.

It also works in the reverse order :)

Create a normal drop-down list

To insert a drop down list in Excel, you use the Data Validation feature. The steps slightly vary depending on whether the source items are in a regular range, named range, or an Excel table.

From my experience, the best option is to create a data validation list from a table. As Excel tables are dynamic by nature, a related dropdown will expand or contract automatically as you add or remove items to/from the table.

For this example, we are going to use the table with the plain name Table1, which resides in A2:A25 in the screenshot below. To make a picklist from this table, the steps are:

  1. Select one or more cells for your dropdown (D3:D7 in our case).
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Allow drop-down box, select List.
  4. In the Source box, enter the formula that indirectly refers to Table1's column named Items.

    =INDIRECT("Table1[Items]")

  5. When done, click OK.
Create a data validation list from a table.

The result will be an expandable and automatically updatable drop-down list that only allows selecting one item.

Tip. If the method described above is not suitable for you for some reason, you can create a dropdown from a regular range or named range. The detailed instructions are here: How to create Excel data validation list.

Insert VBA code to allow multiple selections

This is the core part of the process that does the magic. To turn a regular single-selection picklist into a multi-select dropdown, you need to insert one of these codes in the back end of your target worksheet:

To add VBA code to your worksheet, follow these steps:

  1. Open the Visual Basic Editor by pressing Alt + F11 or clicking the Developer tab > Visual Basic. If you don't have this tab on your Excel ribbon, see how to add Developer tab.
  2. In the Project Explorer pane at the left, double-click on the name of the worksheet that contains your drop-down list. This will open the Code window for that sheet.

    Or you can right-click the sheet's tab and choose View Code from the context menu. This will open the Code window for a given sheet straight away.

  3. In the Code window, paste the VBA code.
  4. Close the VB Editor and save your file as a Macro-Enabled Workbook (.xlsm).
Insert VBA code to select multiple items in dropdown list.

That's it! When you go back to the worksheet, your drop-down list will allow you to select multiple items: Excel drop-down list to select multiple items.

VBA code to select multiple items in dropdown list

Below is the code to make a data validation list that allows selecting multiple items, including repeated selections:

VBA code to select multiple items in Excel dropdown
Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim DelimiterType As String Dim rngDropdown As Range Dim oldValue As String Dim newValue As String DelimiterType = ", " If Destination.Count > 1 Then Exit Sub   On Error Resume Next Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError   If rngDropdown Is Nothing Then GoTo exitError   If Intersect(Destination, rngDropdown) Is Nothing Then 'do nothing Else Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue = "" Then 'do nothing Else If newValue = "" Then 'do nothing Else Destination.Value = oldValue & DelimiterType & newValue ' add new value with delimiter End If End If End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

How this code works:

  • The code enables multiple selections in all drop down lists on a particular sheet. You do not need to specify the target cell or range reference in the code.
  • The code is worksheet specific, so be sure to add it to each sheet where you want to allow multiple selections in drop down lists.
  • This code allows repetition, i.e. selecting the same item several times.
  • The selected items are separated with a comma and a space. To change the delimiter, replace ", " with the character you want in DelimiterType = ", " (line 7 in the code above).

Note. The same character cannot be used as both the delimiter and part of the dropdown items. In our code, the default delimiter is a comma followed by a space (", "), so this combination of characters should not appear anywhere within the dropdown items themselves to avoid conflicts. If you change the delimiter to a single space (" "), bear in mind that the code will only function correctly for single-word items, failing to handle multi-word items containing spaces.

Excel multi-select dropdown without duplicates

When selecting from a large list, users can sometimes pick the same item more than once without noticing. The code below solves the problem of duplicates in a multi-selection data validation drop down list. It lets users pick a particular item only once. If you try to select the same item again, nothing will happen. Pretty cool, right?

VBA code to create multi-select drop down with no repeats
Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType As String DelimiterType = ", "   If Destination.Count > 1 Then Exit Sub   On Error Resume Next Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError   If rngDropdown Is Nothing Then GoTo exitError   If Intersect(Destination, rngDropdown) Is Nothing Then 'do nothing Else Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or _ InStr(1, oldValue, DelimiterType & newValue) Or _ InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then Destination.Value = oldValue Else Destination.Value = oldValue & DelimiterType & newValue End If End If End If End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

Multi-selection dropdown with item removal

When users need to select multiple options but can make mistakes or change their minds during the selection process, a multi selection dropdown that allows for the removal of incorrect items can be a lifesaver.

Consider a scenario where you need to assign multiple tasks to team members using a drop-down list. With Excel's default functionality, the only way to remove an incorrectly assigned task is by clearing the entire contents of the cell and starting over. With the ability to remove individual items from the selection, the team can effortlessly modify task assignments without confusion or errors.

The code below implements the item removal functionality in a simple and intuitive way: the first click on an item adds it to the selection, and a second click on the same item removes it from the selection.

VBA code to create multi-select drop down that allows removing selected items
Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType As String DelimiterType = ", " Dim DelimiterCount As Integer Dim TargetType As Integer Dim i As Integer Dim arr() As String   If Destination.Count > 1 Then Exit Sub On Error Resume Next   Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitError   If rngDropdown Is Nothing Then GoTo exitError   TargetType = 0 TargetType = Destination.Validation.Type If TargetType = 3 Then ' is validation type is "list" Application.ScreenUpdating = False Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list oldValue = Replace(oldValue, DelimiterType, "") oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "") Destination.Value = oldValue ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, newValue & DelimiterType) Or InStr(1, oldValue, DelimiterType & newValue & DelimiterType) Then arr = Split(oldValue, DelimiterType) If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then Destination.Value = oldValue & DelimiterType & newValue Else: Destination.Value = "" For i = 0 To UBound(arr) If arr(i) <> newValue Then Destination.Value = Destination.Value & arr(i) & DelimiterType End If Next i Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType)) End If ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then oldValue = Replace(oldValue, newValue, "") Destination.Value = oldValue Else Destination.Value = oldValue & DelimiterType & newValue End If Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) If Destination.Value <> "" Then If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2) End If End If If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1) End If If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1) End If DelimiterCount = 0 For i = 1 To Len(Destination.Value) If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then DelimiterCount = DelimiterCount + 1 End If Next i If DelimiterCount = 1 Then ' remove delimiter if last character Destination.Value = Replace(Destination.Value, DelimiterType, "") Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "") End If End If End If Application.EnableEvents = True Application.ScreenUpdating = True End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

The below demo highlights how the multi selection dropdown with removal functionality works in Excel. The users can select multiple options from the data validation list and make adjustments on the fly. A streamlined and effective approach to managing selections!
Select and remove multiple items in Excel dropdown.

How to make a multiple selection dropdown with custom delimiter

The character that separates items in the selection is set in the DelimiterType parameter. In all the codes, the default value of this parameter is ", " (a comma and a space) and it is located in line 7. To use a different separator, you can replace ", " with the desired character. For instance:

  • To separate the selected items with a space, use DelimiterType = " ".
  • To separate with a semicolon, use DelimiterType = "; " or DelimiterType = ";" (with or without a space, respectively).
  • To separate with a vertical bar, use DelimiterType = " | ".

For example, if you change the delimiter to a vertical slash, your multi-select picklist will look as follows: A multi-selection dropdown with a custom delimiter

How to create dropdown with multiple selections in separate lines

To get each selection in a separate line in the same cell, set DelimiterType to Vbcrlf. In VBA, it is a constant for the carriage return and line feed characters.

More precisely, you change this code line:

DelimiterType = ","

to this one:

DelimiterType = vbCrLf

As a result, each item that you select from the dropdown list will appear in a new line: A dropdown list with multiple selections in separate lines

How to create multi-select dropdown for specific columns, rows, cells and ranges

All the codes described in this tutorial work across an entire sheet. However, you can easily modify any of the codes, so it only applies to specific cells, rows, or columns as needed. For this, find this line of code:

If rngDropdown Is Nothing Then GoTo exitError

Add immediately after it, add a new line specifying where to allow multiple selections, as explained in the below examples.

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

Where "4" is the number of the target column. In this case, the multi-select dropdown will be only enabled in column D. In all other columns, dropdown lists will be limited to a single selection.

To target several columns, specify their numbers using this code:

If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError

In this case, the multi-select dropdown will be available in columns D (4) and F (6).

Multi-selection dropdown for certain rows

To insert multiple drop-downs in a specific row, use this code:

If Not Destination.Row = 3 Then GoTo exitError

In this example, replace "3" with the row number where you want to enable multi-select dropdowns.

To target multiple rows, the code is as follows:

If Destination.Row <> 3 And Destination.Row <> 5 Then GoTo exitError

Where "3" and "5" are the rows where selecting multiple items is allowed.

Multiple selections in specific cells

To enable multiple selections in particular cells, add one of the below code lines.

For a single cell:

If Not Destination.Address = "$D$3" Then GoTo exitError

For multiple cells:

If Destination.Address <> "$D$3" And Destination.Address <> "$F$6" Then GoTo exitError

Just remember to replace "$D$3" and "$F$6" with the addresses of your target cells.

Multi-select drop-down for specific range

To limit your multi-select dropdown to a particular range, replace this line of code:

If Intersect(Destination, rngDropdown) Is Nothing Then

with this one:

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

The range of interest is specified directly in the code (C3:D10 in the above example). This modification offers a more efficient approach to handing ranges - instead of individually listing 16 cells, you use a single range reference.

How to enable multi-selection functionality in protected sheet

To enable a multi-select dropdown functionality in a protected worksheet, simply insert the following code into the sheet where you've added the primary code. This "protected sheet" code should be added after the main code.

VBA code to allow multiple selections in protected sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect password:="password" On Error GoTo exitError2 If Target.Validation.Type = 3 Then Else ActiveSheet.Protect password:="password" End If Done: Exit Sub exitError2: ActiveSheet.Protect password:="password" End Sub

Before adding this code to your worksheet, remember to replace "password" with the actual password you used to protect the sheet. And this is the only change that needs to be made. The code will automatically detect the presence of a dropdown list in a given cell and allow editing of that cell. In all other cells, editing will remain restricted.

Note. Please be aware that including your real password in the code could lead to a security risk. To ensure the safety of your workbook, store it in a secure location that is protected against unauthorized access or use.

So, there you have it - an Excel dropdown list with multiple selections. Adding this awesome feature to your spreadsheets will increase the accuracy of your data input and help you get your work done faster!

Practice workbook for download

Multi-selection dropdown - examples (.xlsm file)

691 comments

  1. Hi, I'm trying to create a drop-down list with multiple selections in Excel Online. I tried VBA code doesn't work online. Is there another way to achieve this?

  2. Hi, this document has been very helpful. I've copied the Multi-selection dropdown with item removal code. However, it's only allowing multi selection for Row 3. How can I update this? I've tried updating the range with the code above but no luck.

    1. Hello Stephanie!
      Pay attention to the following paragraph of the article above: Multi-select dropdown for specific columns, rows, cells and ranges.
      The code that is provided in the article does not contain row or column restrictions. You can add these restrictions as described in the instructions.

  3. Thanks Alexander for this fantastic tutorial -- it worked perfectly and solved a months-long problem for me. I'm just wondering if there's any way to get Excel to present filter options based on any one item in a drop-down. For example, if cell D13 has multiple items selected ("Apples, Oranges, Pears, Plums, Goose Egg"), is there a way to set up a filter on that column that will show any cell with "Goose Egg"? By default, the filter doesn't seem to care about delimiters, so each list is a new option in the filter options. In other words, I need to scroll through the filter list and check off anything that has "Apples" anywhere in the string in order to see only the cells that contain "Apples". Is there a way to make it so that each list item ("Apples", "Oranges", "Pears", "Plums", "Goose Egg") is a separate item in the filter, or would I need to abandon the multi-item dropdown in order to do that?

    Thanks!

    1. Hello Adrian!
      As I wrote in the comments below, a dropdown list creates a text string. Excel filter can automatically use only this entire string, not its individual elements.

  4. Thank you so much for this code.

    It works perfectly for me if the cells aren't formatted as a table. When I change the formatting to a table and select more than one item I get an error which says "The value in this cell is invalid or missing. Click on 'Display Type Information' for more details".

    When I click on 'Display Type Information' it comes up with the following:

    "Data Type: List
    Restriction: Value must match one of the listed items.

    Blanks will be ignored and are automatically valid."

    I get this error even if the only options in my dropdown list are Yes and No. I tried this as I thought it might be a problem with the length of the options I had originally.

    Are you able to assist?

    1. Hello Adele!
      Unfortunately, I have not been able to reproduce your problem. I have converted the cells that contain the drop down list as a table. The macro works correctly and without errors.

      1. This is so strange. I've even tried opening a new Excel spreadsheet in case there was something corrupt in the old file I was working on, but I still get the same problem. Thank you for trying anyway.

        1. Hi again!

          I've been playing around further and the error doesn't occur when I open the file through Sharepoint and work on it in my browser. The error only occurs when I open Excel. Do you have any idea why this might be?

            1. But this isn't the issue I'm having. The macros work and I don't get error messages when I open through Sharepoint in my browser. When I open it through Excel on my desktop the macros work, but I get error messages...

              1. Sorry - I didn't explain myself clearly. I know that the data is formatted as a table, but the cells where the multi-list is are not formatted as a table. When I format this as a table I get the error. Is it possible to upload a file where both the data and the cells containing the drop down list are formatted as tables so that I can see if I still get an error then?

              2. Hi! For the convenience of other users, we do not upload different copies of the file. However, as I mentioned earlier, formatting the cells with the dropdown list as a table won’t impact the functionality of the multiple selection macro, and there won’t be any errors.

              3. Hi again,

                I opened your sample file. I don't get an error when I open it, but as soon as I format your data as a table, I get an error. Could you upload a sample file where your data is already formatted as tables?

              4. Hi! In the sample file, the data for the drop down list is formatted as a table. If I format the cells containing the drop down list as a table, the error doesn’t show up. Maybe this was a problem with your software. I don't have enough data to give you specific advice.

  5. This may be a dumb question, but is there a way to create a PivotTable taking the individual data from these cells? For example, if I have A-F as options, is there a way to make a PivotTable that shows me all the A's, all the B's, etc.? Because the only thing I can get it to do currently is show "A," "A,C," "B,D," "B,F," and so on and so forth.

  6. Hi Alexander,
    Your step-by-step tutorial above is excellent! The "Multi-selection dropdown with item removal" code works perfectly.

    Is there VBA code or a way to automatically sort the list items in each cell alphabetically once they are added? I have the list items on separate lines in each cell with a carriage return between them for readability.

    For example, if my multi-select list contains:
    Apple
    Banana
    Carrot

    And I select "Carrot" first followed by "Apple", the list currently will show as:
    Carrot
    Apple

    It would be helpful if the multi-select items were forced to display alphabetically as:
    Apple
    Carrot

    We have 73 items in our list and the list itself is sorted but we don't always select the items alphabetically when adding.

    Thank you!

    1. Hi Misho!
      Earlier in the comments below, I mentioned that to sort words in a text string, you need to create a separate VBA macro.

      1. Thank you, Alexander. It worked perfectly.

  7. I have copied your code into my workbook for the Selections on separate lines tab. Unfortunately, my dropdown will still only allow for single selection of items. I thought I was doing something wrong on my own sheet, so I downloaded your sample file to play with it and see exactly how it is working. Unfortunately, when I downloaded that file and started to play with making selection modifications on the "Selections in separate lines" tab, it was no longer allowing me to multiple select any items. For example, I went to the Salad ingredients drop down to unselect "carrot". Now the only item actually showing is "carrot". Is this a versioning issue? Is there something else I can do to fix this?

  8. Hi, when adding the code for protected shield the following error appear: Ambiguous name detected and return to the code page

  9. Hi Alex, thank you for this.
    I tried everything, copied your VBA code for "Multi-selection dropdown with item removal"; although i can view all the items in my drop-down, still im only able to select 1 item from the list.
    can you please help, are there any specific field/settings i need to note? I

  10. Hi, Thanks for the code.
    I am getting the error message " run time error 424" "Object required". Running the debugger then highlights line 12 If Destination.Count > 1 Then. What am I doing wrong? Thanks

    1. Hi! Not enough data to give a concrete answer. I don't know which macro you are using. But in none of the macros is this code written in line 12. Check your code.
      In this code, the Destination object is a cell that changes on the worksheet, and has text or a number written in it.
      If Destination references an object that does not have a Count property (such as a dialog box), you will get this error.

  11. Can you add to the Dropdown with item removal code to create a dependent multi-select dropdown in another column? For instance, I choose 2 properties in one column and that filters the options available to select in another column, Areas of Use?

    1. Hi! I have already answered this question several times in the comments. You can't use Excel's standard tools to create a dependent dropdown list that uses the two values selected in the multiple choice list. You can split those values into separate cells and then create a new list of values for them using the FILTER function. You can use these instructions: How to make a dynamic dependent dropdown list in Excel an easy way.
      Or you can create a dependent drop-down list for multiple values using VBA.

  12. What if the table is on another sheet how to structure the indirect command for it ?

  13. I'm new to this site, but this is actually one of the best articles I've ever read in my entire life. I can't believe that this was published in 2024, thank you so much for being so concise and to the point!

    It's so rare to see actually good articles nowadays, massive kudos for this

  14. Hi, thanks for the info.

    This was working, but I had to save it to a SharePoint site for other team members to access. Is there a way to make this work?

    VBA Code I am using is below (I am trying to apply this to column AM (39) with the items selected on separate lines.

  15. I've added the use multifunction with item removal with the updated password option that is posted in the comments. I save my worksheet with allow edit ranges under the review tab, then select the sort and use autofilter boxes before entering my password. The multiselect works perfectly with no error messages but it doesn't allow for me to sort anymore. When I go back in to unlock, the autofilter and sort options have been deselected. How do I fix this?

    Thank you!

    1. Hello Loretta!
      Unfortunately, I can't see your workbook and your data. However, you can only select and add multiple values to the text string in a cell using the Multiple Selection drop-down list. It does not have any effect on your ability to sort your data. However, you will not be able to sort the data by a single word from that text string.

  16. Alexander, First: you are a superstar. Second: Is there any way to parse the final contents of the multi-select list such that I can split them into individual items for a =FILTER function? By way of example, lets say my validation list={Apples, Bananas, Oranges, Pineapples} (obviously with this being a list in multiple rows of a single column in excel set as the data validation source). If I put the final result of the data validation list in F1, would there be any way to use =CHOOSECOLS(TEXTSPLIT(MyEvalCell, ","), 2) to get the second item like this =FILTER(A1:A200,(B1:B200=(choosecols(textsplit(F1,","),1))+((B1:B200=(choosecols(textsplit(F1,","),2))) etc?

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

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

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

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

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