How to Make Multiple Selections in a Drop Down List in Excel

Written by Last updated October 22, 2026

📋 FREE EXCEL TIPS EBOOK - Click here to get your copy

You can use VBA to select multiple items from an Excel drop-down list and keep them together in the same cell.

One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel.

When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.

He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.

Something as shown below in the pic:

New York and York selected together in cell C2, with the city drop-down open.

In this tutorial, I will show you how to make multiple selections in an Excel drop-down list (with repetition and without repetition).

This has been one of the most popular Excel tutorials on this site.

Follow along with the example file

Multiple Selections in Excel Drop Down List.xlsm

This Article Covers

Watch Video – How to Select Multiple Items from an Excel Drop Down List

The video shows the original approach. Use the code below for the examples here, including the corrected check that treats New York and York as different cities.

Create the Drop-Down List

There are two parts to creating a drop-down list that allows multiple selections: creating the list and adding the VBA code to the worksheet.

Excel’s standard Data Validation list doesn’t have a setting that keeps successive choices in one cell. The VBA below adds that behavior; a formula or Office Script isn’t a direct replacement for this selection-change event.

These examples were tested in desktop Excel on Windows. Excel for the web cannot run VBA, and this workbook has not been tested on Mac.

Below I have a list of cities, and I want to select several of them in cell C2.

Five source cities in column A and an empty Selected Cities cell C2.

The example file already has the lists and code. If you’re building this in your own workbook, enter the five cities in A2:A6, then follow these steps:

Select cell C2, or select the range of cells where you want the drop-down lists.

Cell C2 selected before creating its drop-down list.

Click the Data tab.

The Data tab on the Excel ribbon.

Click Data Validation in the Data Tools group.

The Data Validation dialog before choosing a validation type.

On the Settings tab, choose List from the Allow menu.

List selected in the Allow field of Data Validation.

In the Source field, enter =$A$2:$A$6.

The Data Validation Source field set to =$A$2:$A$6.

Click OK.

The completed city drop-down list in C2.

Now, cell C2 has a drop-down list showing the cities in A2:A6. At this point, choosing another city still replaces the previous one.

Select Multiple Items Without Duplicates

A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.

Below is the city list from the No Duplicates sheet, where the selections will go into C2.

The No Duplicates example before selecting cities.

Here is the VBA code. Copy the entire block, including the helper function at the end:

Option Explicit

'Paste into the worksheet's code module, not a standard module.
'Use the drop-down or type one valid choice. Bulk edits replace values.
'Ordinary paste can replace validation. Use Paste Values for a single choice.
'Other VBA that writes to these inputs must disable events around its writes.
Private Sub Worksheet_Change(ByVal Target As Range)
    Const InputCells As String = "C2"
    Const Separator As String = ", "
    Const AllowRepeats As Boolean = False
    Dim NewValue As String, OldValue As String, Result As String
    Dim Entry As Variant, AlreadySelected As Boolean
    Dim PreviousEvents As Boolean, EventsChanged As Boolean
    Dim Undone As Boolean, Failure As String
    Dim ValidChoice As Boolean

    If Target.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range(InputCells)) Is Nothing Then Exit Sub
    If Not HasListValidation(Target) Then Exit Sub
    On Error GoTo Failed
    If Not IsError(Target.Value2) Then NewValue = CStr(Target.Value2)
    If Len(NewValue) = 0 And Not Target.HasFormula And Not IsError(Target.Value2) Then Exit Sub
    ValidChoice = Target.Validation.Value And Not Target.HasFormula And Not IsError(Target.Value2)

    PreviousEvents = Application.EnableEvents
    Application.EnableEvents = False
    EventsChanged = True
    Application.Undo
    Undone = True
    If IsError(Target.Value2) Or Target.HasFormula Then
        OldValue = ""
    Else
        OldValue = CStr(Target.Value2)
    End If

    If Not ValidChoice Then
        Failure = "Choose one city from the drop-down list. The previous selections have been kept."
        GoTo Finished
    End If

    'The separator cannot also appear inside an individual list item.
    If InStr(1, NewValue, Separator, vbBinaryCompare) > 0 Then
        Failure = "This item contains the selection separator. Choose a separator that is absent from the list items."
        GoTo Finished
    End If

    If Len(OldValue) = 0 Then
        Result = NewValue
    Else
        If Not AllowRepeats Then
            For Each Entry In Split(OldValue, Separator)
                If StrComp(CStr(Entry), NewValue, vbTextCompare) = 0 Then
                    AlreadySelected = True
                    Exit For
                End If
            Next Entry
        End If
        If AlreadySelected Then
            Result = OldValue
        Else
            Result = OldValue & Separator & NewValue
        End If
    End If
    If Len(Result) > 32767 Then
        Failure = "The selections would exceed Excel's cell text limit. Clear this cell and start a shorter list."
        GoTo Finished
    End If
    Target.Value2 = Result

Finished:
    If EventsChanged Then Application.EnableEvents = PreviousEvents
    If Len(Failure) > 0 Then MsgBox Failure, vbExclamation, "Multiple selections"
    Exit Sub

Failed:
    Failure = "The selections could not be combined: " & Err.Description
    'Undo may be unavailable for a programmatic edit. Do not undo again.
    On Error Resume Next
    If Undone Then Target.Value2 = OldValue
    If EventsChanged Then Application.EnableEvents = PreviousEvents
    On Error GoTo 0
    MsgBox Failure, vbExclamation, "Multiple selections"
End Sub

Private Function HasListValidation(ByVal Cell As Range) As Boolean
    On Error Resume Next
    HasListValidation = (Cell.Validation.Type = xlValidateList)
    On Error GoTo 0
End Function

Option Explicit'Paste into the worksheet's code module, not a standard module.'Use the drop-down or type one valid choice. Bulk edits replace values.'Ordinary paste can replace validation. Use Paste Values for a single choice.'Other VBA that writes to these inputs must disable events around its writes.Private Sub Worksheet_Change(ByVal Target As Range)    Const InputCells As String = "C2"    Const Separator As String = ", "    Const AllowRepeats As Boolean = False    Dim NewValue As String, OldValue As String, Result As String    Dim Entry As Variant, AlreadySelected As Boolean    Dim PreviousEvents As Boolean, EventsChanged As Boolean    Dim Undone As Boolean, Failure As String    Dim ValidChoice As Boolean    If Target.CountLarge <> 1 Then Exit Sub    If Intersect(Target, Me.Range(InputCells)) Is Nothing Then Exit Sub    If Not HasListValidation(Target) Then Exit Sub    On Error GoTo Failed    If Not IsError(Target.Value2) Then NewValue = CStr(Target.Value2)    If Len(NewValue) = 0 And Not Target.HasFormula And Not IsError(Target.Value2) Then Exit Sub    ValidChoice = Target.Validation.Value And Not Target.HasFormula And Not IsError(Target.Value2)    PreviousEvents = Application.EnableEvents    Application.EnableEvents = False    EventsChanged = True    Application.Undo    Undone = True    If IsError(Target.Value2) Or Target.HasFormula Then        OldValue = ""    Else        OldValue = CStr(Target.Value2)    End If    If Not ValidChoice Then        Failure = "Choose one city from the drop-down list. The previous selections have been kept."        GoTo Finished    End If    'The separator cannot also appear inside an individual list item.    If InStr(1, NewValue, Separator, vbBinaryCompare) > 0 Then        Failure = "This item contains the selection separator. Choose a separator that is absent from the list items."        GoTo Finished    End If    If Len(OldValue) = 0 Then        Result = NewValue    Else        If Not AllowRepeats Then            For Each Entry In Split(OldValue, Separator)                If StrComp(CStr(Entry), NewValue, vbTextCompare) = 0 Then                    AlreadySelected = True                    Exit For                End If            Next Entry        End If        If AlreadySelected Then            Result = OldValue        Else            Result = OldValue & Separator & NewValue        End If    End If    If Len(Result) > 32767 Then        Failure = "The selections would exceed Excel's cell text limit. Clear this cell and start a shorter list."        GoTo Finished    End If    Target.Value2 = ResultFinished:    If EventsChanged Then Application.EnableEvents = PreviousEvents    If Len(Failure) > 0 Then MsgBox Failure, vbExclamation, "Multiple selections"    Exit SubFailed:    Failure = "The selections could not be combined: " & Err.Description    'Undo may be unavailable for a programmatic edit. Do not undo again.    On Error Resume Next    If Undone Then Target.Value2 = OldValue    If EventsChanged Then Application.EnableEvents = PreviousEvents    On Error GoTo 0    MsgBox Failure, vbExclamation, "Multiple selections"End SubPrivate Function HasListValidation(ByVal Cell As Range) As Boolean    On Error Resume Next    HasListValidation = (Cell.Validation.Type = xlValidateList)    On Error GoTo 0End Function

VBA example by Sumit Bansal, https://trumpexcel.com.

This is a worksheet event: Excel runs it when you change the selection cell. Put it in that worksheet’s code window, not a standard module, and don’t press F5 to run it.

Here are the steps to add it to your workbook:

Press Alt + F11 to open the Visual Basic Editor.

The Visual Basic Editor and Project Explorer for the example workbook.

You can also open the editor from the Developer Tab by clicking Visual Basic. If the Project Explorer pane is missing, press Ctrl + R to show it.

In Project Explorer, expand your workbook’s Microsoft Excel Objects folder.

The example workbook expanded in Project Explorer with its worksheets under Microsoft Excel Objects.

Double-click the worksheet that contains the drop-down list.

The empty No Duplicates worksheet code window.

Paste the complete code into that worksheet’s code window.

The complete worksheet event code installed in the No Duplicates worksheet, showing its opening lines.

If that sheet already has a Worksheet_Change procedure, don’t paste a second one below it. The two pieces of event code need to be combined into one procedure.

Close the Visual Basic Editor to return to Excel.

Cell C2 ready for city selections after returning from the Visual Basic Editor.

Save the workbook as Excel Macro-Enabled Workbook (*.xlsm).

Excel Macro-Enabled Workbook (*.xlsm) selected in the Save As dialog.

The older .xls format can also store VBA, but use .xlsm for this workbook. Saving as .xlsx removes the VBA needed for multiple selections.

When reopening the workbook, enable macros only if you trust the file. If your organization blocks macros, check with your IT team; you don’t need to enable Trust access to the VBA project object model to run this code.

To try the downloaded No Duplicates sheet, clear C2 first because it contains sample selections. Then choose New York followed by York from the drop-down.

New York and York retained as separate choices in C2.

You get New York, York in C2. If you select York again, the value stays the same, because the code compares complete city names rather than looking for one name inside another.

The comparison ignores capitalization, so Boston and BOSTON count as the same choice. Selecting an existing city does not remove it; press Delete to clear the cell and choose the cities again.

The code briefly undoes the new selection to read what was in the cell before, then combines the values. It turns events off while writing the result so that its own change doesn’t trigger the same code again.

Important: This macro uses Excel’s Undo history internally, so you cannot rely on Ctrl + Z to undo its changes. Keep a copy of the workbook before making substantial changes.

Use the drop-down to add one city at a time. Typing one valid city also works, but replacing the cell with a whole comma-separated list isn’t the same as making individual selections.

Ordinary paste can replace the cell’s Data Validation rule and stop the multiple-selection behavior. Use Paste Values for a single choice; the code rejects a value outside the list while keeping your previous selections.

Changes to several cells at once are left as ordinary edits, rather than being appended. If other VBA writes to these cells, that code must disable events around its writes because programmatic changes don’t create the same Undo history as your selections.

Allow Repeated Selections

The Allow Repeats sheet has the repetition version already set up in C2. Note that if you select an item more than once, it will be entered again (repetition is allowed).

Below is the same city list, with the repeated choices kept together in C2.

The Allow Repeats example with C2 cleared before selecting cities.

In the worksheet code, replace the AllowRepeats line with this one:

Const AllowRepeats As Boolean = True

Const AllowRepeats As Boolean = True

Keep the rest of the code, including the helper function, unchanged. Use this setting in the existing procedure rather than adding another copy of Worksheet_Change.

Clear C2 on the downloaded sheet before trying it, because the cell already contains sample selections. Then selecting Boston, Austin, and Boston again gives you Boston, Austin, Boston.

Boston, Austin, and Boston retained together in C2.

Follow along with the example file

Multiple Selections in Excel Drop Down List.xlsm

Use Multiple Selection in Other Cells

Since I get a lot of similar questions, I have created the following sections to answer some of the most asked questions about this tutorial and the VBA code.

If you have any questions, I request you to go through these sections first.

The code starts with C2 as its target. On the Several Cells sheet, C2, C3, and C4 each keep their own selections from the city list.

Cells C2, C3, and C4 contain independent selections from the same city list.

To use those three cells, replace the InputCells line near the top of the code with:

Const InputCells As String = "C2:C4"

Const InputCells As String = "C2:C4"

Make sure each target cell also has a List validation rule. Changing this line tells the macro where to work; it doesn’t create the drop-down lists.

You can use the following range variations in your own workbook. Replace the same InputCells line with the one you need, rather than adding all of them.

For every list-validation cell in column C:

Const InputCells As String = "C:C"

Const InputCells As String = "C:C"

For columns C and D:

Const InputCells As String = "C:D"

Const InputCells As String = "C:D"

For row 2:

Const InputCells As String = "2:2"

Const InputCells As String = "2:2"

For rows 2 and 3:

Const InputCells As String = "2:3"

Const InputCells As String = "2:3"

Cells without List validation are ignored, even if they’re inside the specified range. Each worksheet that needs this behavior must have its own event code.

These examples accumulate choices within each cell. If you want a choice used in one cell to disappear from other cells’ lists, see Creating Multiple Drop-down Lists in Excel without Repetition.

Change the Separator or Put Each Selection on a New Line

The comma examples use a comma followed by a space between choices. The Separator setting controls both how values are joined and how the no-duplicates version identifies individual choices.

To use a vertical bar followed by a space, replace that setting with:

Const Separator As String = "| "

Const Separator As String = "| "

You can use a space alone with a list of single-word choices:

Const Separator As String = " "

Const Separator As String = " "

A space alone won’t work with this example’s New York entry because that city already contains a space. Choose a separator that appears in none of your list items; the code rejects a choice containing the separator.

Clear any existing selections before changing the separator. Otherwise, the old text still uses the previous separator, so the code cannot reliably split it into individual choices.

For a new line in the same cell, use the Line Breaks sheet. Below is the city list with selections displayed on separate lines in C2.

New York, York, and Boston displayed on separate lines in C2.

In that sheet’s code, the separator is:

Const Separator As String = vbLf

Const Separator As String = vbLf

vbLf supplies the line-feed character Excel uses inside a cell. The original example used vbNewLine; this version uses vbLf to insert the Excel cell line break directly.

If you’re adding this version to your own sheet, make the line breaks visible:

Select the cell containing the selections.

Cell C2 selected before enabling Wrap Text.

On the Home tab, click Wrap Text.

Wrap Text on the Home tab of the Excel ribbon.

Increase the row height so that all the selected cities are visible.

The increased row height makes all selected cities visible in C2.

The example file reserves enough height for all five cities. Selecting New York, York, and Boston puts each city on its own line instead of separating them with commas.

Use the Drop-Down on a Protected Sheet

You can keep the city source list protected while allowing selections in C2. The Protected Sheet example does this by leaving C2 unlocked before protecting the worksheet.

The protected-sheet example keeps the selected cities in editable cell C2.

For your own worksheet, set up the drop-down and event code before applying protection. Then follow these steps:

Select the cells that should remain editable, such as C2.

Cell C2 selected as the input that will remain editable.

Press Ctrl + 1 to open Format Cells.

The Format Cells dialog opened for C2.

On the Protection tab, clear the Locked checkbox.

The Locked checkbox cleared on the Protection tab for C2.

Click OK.

Cell C2 after its Locked setting has been cleared.

On the Review tab, click Protect Sheet.

The Protect Sheet dialog with selection of unlocked cells allowed.

Choose your protection settings and click OK, confirming your password if you set one.

The city drop-down remains available in unlocked C2 on the protected worksheet.

The macro writes only to the unlocked selection cell, so this version doesn’t need Me.Protect UserInterfaceOnly:=True. The protected example was tested again after closing and reopening the workbook.

The example sheet uses demo as its demonstration password. If you need to change its source list, unprotect the sheet first; for more detail on choosing editable cells, see Lock Cells in Excel.

Follow along with the example file

Multiple Selections in Excel Drop Down List.xlsm

You can now keep several choices in one cell, with or without repeated items. Save the workbook as .xlsm so the code stays with it when you reopen it.

Other Excel Articles You May Also Like:

Sumit Bansal

13x Microsoft Excel MVP

Hey! I'm Sumit Bansal, founder of trumpexcel.com and a Microsoft Excel MVP. I started this site in 2013 because I genuinely love Microsoft Excel (yes, really!) and wanted to share that passion through easy Excel tutorials, tips, and Excel training videos. My goal is straightforward: help you master Excel skills so you can work smarter, boost productivity, and maybe even enjoy spreadsheets along the way!

LinkedIn YouTube X (Twitter)

601 thoughts on “How to Make Multiple Selections in a Drop Down List in Excel”

DMM

November 2020 at 5:13 pm

AWESOME, easy peasy (non techie here!)
Reply
Shalini

November 2020 at 9:27 pm

In the code window, when you put in the code, the drop down on top changes from Declarations to Change. Mine is not doing that and the code is not working. Please help
Reply
sina

November 2020 at 3:11 pm

hello thanks for great help.i have a problem. i use vlookup for my list and when choose a item in list the price of item show in other cell. now i want when choose multi item in list sum the price of item sum and show. please help me
Reply
Kimsan

October 2020 at 11:58 am

How can I do for multiple drop down lists for each cells in the same column? For example, in Column A, a cell as in A1, A2, and A3.
Reply
Anu

October 2020 at 11:13 pm

How to get the count of selected items instead of those values?
Reply
Anu

October 2020 at 11:12 pm

How can I get the count of selected values of drop down list insted of those values?
Reply
Maneet

October 2020 at 7:33 am

Need 2 multi pick list in same sheet how to use this code?
Reply
Jordan Peters

October 2020 at 8:16 pm

Doesn’t work
Reply
Rangeeta

October 2020 at 4:32 pm

Thanks Sumit for the excellent tip. Please suggest how do I apply it to multiple columns in the same work sheet?
Reply
Pete

October 2020 at 10:51 pm

How do I sort the data once it its entered? If, for example, my dropdown list contained the values: One, Two, Three, Four and someone were to select them in the following order: Two, One, Four, Three. How would I go about re-ordering the entry to coincide with the list order?
Reply
Filip

October 2020 at 5:43 pm

Im want to use multiple selection list in many sheets in one work book. Each list, in each workbook is in different cell but all the lists are created from same named range, eg. Multiplelist. Can I use that code for all lists created from named range Multiplelist in whole workbook?
Reply
Lokendra

October 2020 at 8:46 am

can i make multiple selection in different coulmn of a single sheet?
Reply
Ossie

September 2020 at 9:46 pm

This does not seem to work if I protect the sheet or workbook. How can I get it to work on a protected sheet
Reply
Dee Reinhardt

September 2020 at 5:16 am

What happens if I want to use a multi-select in different columns and the data validation list options are different for the columns? Does the VB code for the various columns need to be on different sheets? or do the lists just need to be different names?
Reply
Sam

September 2020 at 3:03 pm

How to Make Multiple Selections in a Drop Down List in Excel (non repeating)

I don’t know what I am doing wrong. I cannot make this work.
Reply
Natasha

September 2020 at 2:59 pm

Hi, the coding has applied itself to the whole sheet instead of the target columns I have told it to use? How do I overcome this?
Reply
christine

September 2020 at 4:47 am

I want to do the same thing in each column from “B” through “R” whats the easiest way to be able to add this to the code?
Reply
kathruth1@gmail.com

September 2020 at 7:51 pm

How can I remove already selected word from the drop-down list? example
selected: one, two, three …..now I need to remove three and
re-select four
Reply
- karin
  
  October 2020 at 8:36 pm
  
  i have the same question, did you solve it already?
  Reply
- Hadar
  
  October 2020 at 2:31 pm
  
  I have the same problem do you have code for this ?
  Reply
- Philipp
  
  November 2020 at 6:46 pm
  
  If there is a known solution I would love to hear it 🙂
  Reply
  - Matt
    
    October 2023 at 10:35 pm
    
    I have the same question, any solution to this?
    Reply
Colm

September 2020 at 6:52 pm

Honestly can’t get this to work. I want to reference a list defined in Worksheet X from cells in Worksheet Y. I have a range of values in consecutive cells in Worksheet X – let’s call them A, B, C, D, E. I’ve copied the code above and no joy. First question: how do I reference Worksheet X from cells in Worksheet Y – do I need to change the $C$2 in the example code to something like =X!$A1:$A5 ? Does this VBA code need to be in Worksheet Y or X?
Reply
CWT

August 2020 at 12:25 pm

Hi I just wonder if you can apply this multiple selection drop-down list to a whole column but only in specific table? Thanks
Reply
Anna

August 2020 at 9:17 pm

This is highly useful, many thanks!
However I do have a question: How can I achieve that in the filter function (in the headline of any column), the different options do appear separately even if multiple items have been chosen in the fields of the table ? Many thanks for any hint!
Reply
lorraine

August 2020 at 8:34 pm

This is so helpful thank you! I was wondering, is there any way to create a pivot table from this which give information about how often one of the options is mentioned. So for example, lets say I have a table of fruit choices for 5 people (separated below with 😉 with drop down list as above, the responses are banana, apple, orange; apple, orange; banana, orange; apple; banana, apple, is there a way to run a pivot table so that I know how many people chose banana, how many chose apple, etc. I’ve tried it but it’s only giving me numbers for their full responses. Hope that makes sense!
Reply
Sana Ali

August 2020 at 9:12 pm

Life saver! Thanks a lot Sumit!
Reply
Lemnaru Mihai

August 2020 at 3:41 pm

I restricted the code to work in a range of cells, and I added “Please select!” in the cells to apperar when you press delete to cleare it.
Works fine for me with the code below.
Good luck!

Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range(“M6:M20”)) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Or Oldvalue = “Please select!” Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If

Application.EnableEvents = True
Exitsub:
If ActiveCell.Value = “” Then
Target.Value = “Please select!”
End If
Application.EnableEvents = True
Exit Sub
End If
End Sub
Reply
Erik

July 2020 at 12:46 am

Removing/deleting an item in your list (eg after clicking a wrong one) and at the same time also removing leading and trailing spaces and comma’s works fine for me with the code below. Good luck.

If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
NewValue = Target.Value
Application.Undo
OldValue = Target.Value
If OldValue = “” Then
Target.Value = NewValue
Else
If InStr(1, OldValue, NewValue) = 0 Then
Target.Value = OldValue & “, ” & NewValue
Else:
RemoveLocation = InStr(1, OldValue, NewValue)
ItemLength = Len(NewValue)
If Len(OldValue) Len(NewValue) Then Target.Value = Left(OldValue, RemoveLocation – 1) & Mid(OldValue, RemoveLocation + ItemLength + 2)
End If
End If
‘remove leading and trailing spaces en comma’s
Do While True
If Left(Target.Value, 1) = ” ” Or Left(Target.Value, 1) = “,” Then
Target.Value = Mid(Target.Value, 2)
Else
Exit Do
End If
Loop
Do While True
l = Len(Target.Value)
If Right(Target.Value, 1) = ” ” Or Right(Target.Value, 1) = “,” Then
Target.Value = Left(Target.Value, l – 1)
Else
Exit Do
End If
Loop
End If
Reply
- Tao Levy
  
  September 2020 at 10:04 pm
  
  Where would I place this code relative to the original one provided by Sumit? Thanks
  Reply
- Pav
  
  September 2020 at 4:20 am
  
  Hello, I’m getting an error on this line –
  If Len(OldValue) Len(NewValue) Then …
  Any thoughts? Thanks.
  Reply
phitfa

July 2020 at 4:16 pm

i need help, please do to assist me sir.
my problem is how to make dropdown list in excel displayed in 3 rows.
eg:
A
B
C
when A (row 1) is chosen then B and C also automatically displayed in row 2 and 3.
please assist me sir, and thank you so much for your attention.
Reply
- Lemnaru Mihai
  
  August 2020 at 3:43 pm
  
  Hi!
  Try the code I posted above.
  Reply
Avanthi

July 2020 at 7:44 am

Hi
This is a very useful and simply explained video. I have watched few videos. However, this one is the best and simple. like it very much. Thanks.

I need to know how to apply this in multiple columns
Reply
Rola

July 2020 at 11:20 pm

Hi, the macro is working well but when I write in the cell and or erase it keeps the old and new value. How can i modify the macro to make erasing possible? thank you
Reply
Eko

July 2020 at 8:07 am

Summit, thank you for this great VBA code. How to save this vba code with my worksheet? Everytime I close my workbook then reopen it, the VBA code is gone and I have start it all over again?
Reply
Ihsan

July 2020 at 12:18 am

sir
I need all Excel sheet in all drop down list multiply selection kindly send code or excel formula apply.
Thanks
Reply
MinBee

July 2020 at 2:35 am

Sumit, this an is excellent macro, thank you. One thing, I have the macro set to return the results with each item on a new line. however, the cell height is not adjusting and my responses are getting cut off. I do have the Wrap text option selected for the cells. I also have the selection macro running on more than one row however it is running all in the same column, so the macro would need to run regardless of the row the data is in. Would you be able to write/or tell me an addition to the macro that will adjust the cell height after it is filled?

thank you so much for your help
Reply
Sabrina

July 2020 at 10:10 pm

thank you for this great tutorial! I was able to set everything up but then when I tested it , I was still only able to pick one item only. I copy pasted the exact code. can you elaborate on why this would happen? thanks
Sabrina
Reply
Hazel

July 2020 at 3:56 pm

I used this code a year ago and it worked. Now I wanted to use it again and it isn’t working anymore. I retrieved my old file from last year and the multiple selection I used is gone, only one of the options is left. I downloaded the sample file from this webpage and it isn’t working on this file. HasExcel changed in the meantime and do I need to modify the code?
Reply
Jean

June 2020 at 9:02 pm

Hi Sumit Bansal, thank you so much for your help with the coding – I cannot tell you how much it has helped me and made my life so much easier :). Please could you advise if you are able to help with creating a list box in word with the functionality to select multiple dropdowns? I have searched the web and been unsuccessful. Your help will be greatly appreciated. Many thanks.
Reply
Tracey

June 2020 at 2:09 am

Can you make multi selection drop down boxes in more than one column?
Reply
mary

June 2020 at 7:23 pm

This works great until I save it and try it again. After starting the spreadsheet again, the functionality fails. I tried saving it as an .xlsm. Any ideas
Reply
BabaC.

June 2020 at 4:56 am

Thank you very much for this tutorial, this was exactly what I needed. However, when I use Filter function in this column to sort results, it does not recognize individual word in cells with multiple choices. As exemple, “xxx” will be recognized if standing allone in the cell, but not in cells with two choices, like in “xxx, yyy” – it simply does not appear in filtered results at all. Is there any way to correct this? Thank you!
Reply
CJ

June 2020 at 12:38 am

My scenario. My first column displays a list. The second column displays a list depending on the first column selection. I am able to make multiple selections in the first column. However, the next column will not let me make selections off of the first columns multi-selections if i choose more than one.
Reply
David Iman

June 2020 at 10:39 pm

What if I select an item and then want to deselect it from list?
Reply
- Olaf
  
  June 2020 at 5:51 pm
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  ‘ To allow multiple selections in a Drop Down List in Excel (remove when selection is repeated)
  Dim Oldvalue As String
  Dim Newvalue As String
  Application.EnableEvents = True
  On Error GoTo Exitsub
  If Target.Column = 35 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
  GoTo Exitsub
  Else:
  If Target.Value = “” Then
  GoTo Exitsub
  Else:
  Application.EnableEvents = False
  Newvalue = Target.Value
  Application.Undo
  Oldvalue = Target.Value
  If Oldvalue = “” Then
  Target.Value = Newvalue
  Else:
  If Oldvalue = Newvalue Then
  Target.Value = “”
  ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
  Target.Value = Oldvalue & “, ” & Newvalue
  ElseIf InStr(1, Oldvalue, Newvalue) = 1 Then
  Target.Value = Replace(Oldvalue, Newvalue & “, “, “”)
  Else:
  Target.Value = Replace(Oldvalue, “, ” & Newvalue, “”)
  End If
  End If
  End If
  End If
  End If
  Application.EnableEvents = True
  Exitsub:
  Application.EnableEvents = True
  End Sub
  Reply
  - MinBee
    
    July 2020 at 4:36 am
    
    I have tried this code and it seems to remove a selected item, but only if nothing else has been selected. That is, if I select item A, and then select item A again, it will disapear. However if I select item A and item B then click item A again to remove it, it does not remove item A. I just does nothing.
    
    Would you have any further suggestions?
    
    Thank you
    Reply
    - Olaf
      
      July 2020 at 6:13 pm
      
      Hi Minbee,
      In my workbook it works with this code. I can remove any item what I want. So also Item A after an item B is selected. So I am not sure why it doesn’t work in your version.
      The only thing you have to adjust is the column number. (35 in my example) but I assume that you have done that.
      
      Maybe you can post your code here then I can have a look.
      Reply
  - Beth T.
    
    July 2020 at 5:07 am
    
    Thank you so much! This worked great!
    Reply
Ron

June 2020 at 7:31 pm

Hi,
I was excited to see your video on this. I have a range that I need to apply this to and it looks like this is mostly for a couple of columns or cells. I need this for a range of D17:W76. Is there a way to alter your code to accomplish this?
Reply
Dan

June 2020 at 11:17 pm

Hi,
I’ve created a worksheet with the multiple selection functionality in a protected sheet, however I can’t get the VBA code to run whilst the spreadsheet is shared with multiple users. Please could you help or provide any suggestions for a novice like me. Thanks. Dan.
Reply
Paul Ortiz

June 2020 at 10:27 pm

Hey! Thanks for this – super useful. Quick question; I need this code to appear twice in the same sheet. Once with “, ” and once with ” “. When I try to use them both I get compile errors. Appreciate any help!!
Reply
Carlos H.

June 2020 at 9:13 pm

How can I use the non repetition code for multiple columns on the same report?
Reply
Raffaele

June 2020 at 1:04 am

Works like a charm. THANK YOU!
Reply
MARSHALL SLOANE

May 2020 at 2:59 am

I am using the code that allows repetition, but when i select the same value multiple times it does not append the target.value. For instance i select 0.1, 0.2, 0.1 and the output is just 0.1, 0.2
Reply
Chris

May 2020 at 5:24 pm

Tried it in a row. Worked perfectly well . Thanks
Reply
Wendy Farnham

May 2020 at 12:51 pm

when i try to enter the line ” Target.Value = Oldvalue & “, ” & Newvalue” i get an error message, it highlights the “,” and a box pops up Saying compile error “expected end of statement. What do i need to do to fix that.
Reply
mounah

May 2020 at 4:23 pm

Hi, could you please provide a code for the same first example above such that I can delete a value after choosing it from the drop list?
Reply
Marta

May 2020 at 4:23 pm

I have 3 columns in which I wanted to apply the multichoice option, but each column has its own options. Is it possible to do this?

Thanks!
Reply
- NUR HANNA
  
  June 2020 at 1:29 pm
  
  i want to do this as well. can anybody help?
  Reply
SEB

May 2020 at 12:13 pm

This looks great but for some reason the code does not work here… I copy paste it but still I cannot make multiple choices from the list. :- appreciate some help!
Reply
- SEN
  
  June 2020 at 7:09 pm
  
  did you get an answer? I am having the same trouble.
  Reply

Leave a Comment Cancel reply