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”

Maria

January 2019 at 11:26 am

This was exactly what I needed. Followed your steps and it worked perfectly! Thank you!!
Reply
Vanessa

January 2019 at 9:09 pm

Using your multiple option list – I am trying to put in more than “one change action” on a worksheet and it wasn’t working. I tried a few more things and still no dice.

Do you have the code that you could share? I am sure there is a trick to it.
Vanessa
Reply
Angela Wilson

January 2019 at 10:46 pm

I have followed all of the steps that you have provided and i am getting an error. – Compile error- what does this mean and what do i need to do to fix it so that i may be able to select multiple different items in the drop down menu. I am also needing this to go from R2 to R300
Reply
sachin

January 2019 at 2:35 pm

How I can have the different logic in 3 different column of same sheet. Please suggest
Reply
Rob

January 2019 at 1:46 pm

When I protect the worksheet, it will not allow users to multiselect in the cell. Is there a way to fix that?
Reply
Chintan Bhomia

December 2018 at 3:51 am

This is great. Thank you for sharing the code. I want to do more out of this, however, there are some problems.For eg., I cannot delete anything added by error. My teammates running this on Mac face problem where the highlighted value from the list gets added if one wishes to abort by pressing ESC.
Any ideas on how to implement such changes ?
Reply
Stephen Marvin

December 2018 at 3:30 am

FANTASTIC! WORKED LIKE A CHARM Thank you.
Reply
Patty

December 2018 at 9:26 am

How can i make multiple selection dropdownlist work in all columns of a worbook
Reply
RAMADAN

November 2018 at 12:19 pm

Thanks for this.

how to make the list visible while selecting more than one item from the list. now if i want to select 3 items from my list, i have to click 3 times and select each one separately. would more convenient if the list stays open until i finish selecting my items.
Reply
- Jeff
  
  June 2020 at 12:56 am
  
  Yes….this!!! How to keep the drop down open?
  Reply
Benny

November 2018 at 7:12 am

Thank you, this is helpful! In my list, I have a wildcard (Other: ***), that I want people to be able to replace with an additional response. However, when I do that with the above code, it copies what I have listed and then shows it again with my changes.

i.e.: First choose “Option 1”, then chose “Option: ***” which I change to “Option 4”. The output is “Option 1,Option ***,Option 1, Option 4”. I want to get rid of “Option ***” and “Option 4”.

Any suggestions?
Reply
- Rahil
  
  March 2019 at 10:30 pm
  
  Thank you, Sumit for this very helpful instruction. I have the same issue as Benny. I have a list of stakeholder names and want to provide people with the option to write the name of a stakeholder outside of my list. Disabling the Error Alert in data validation allows me to do that. However, when someone selects an option (e.g., finance) from the list and then writes the name of a new stakeholder, I get: Finance, New Stakeholder, Finance, New Stakeholder.
  
  on the other hand, if someone types the name of the new stakeholder first and then select from the drop-down list, I get: New stakeholder, Finance
  
  Is there any way to fix this to make sure we get the same results whether people type before or after selecting from the existing list?
  Thanks again.
  Reply
brett watkin

November 2018 at 7:59 pm

Your code has nearly got me to what i need done , Many thanks. I just need to be able to select more than one on a drop down list on spacific cells, Currently any drop down box in colum c but ideally need to just choice for eg c13, c16 ,e16 can this be done
Reply
brett watkin

November 2018 at 7:35 pm

I need to add multiple selection in just spacific cells can this be done
Reply
Amir Manzoor

November 2018 at 4:11 am

I need to create multiple drop-downs in multiple columns (like C and F). How do I get this for all the cells in these columns with multi-select functionality?
Reply
zulfiqar ali

November 2018 at 12:07 pm

dear sir,
very informative. can you please tell me how to apply multiple selections in multiple column in a protected sheet?
Reply
John

November 2018 at 1:18 am

I have Excel 2010 and cant seem to get this to work. Any ideas?
Reply
Mike

November 2018 at 11:04 pm

Amazing! Thank you so much for sharing your expertise! I had never used any code of any kind in Excel and your step-by-step tutorial made this simple to execute.
Reply
Brandon Ch

November 2018 at 7:45 pm

Is there any way to modify the code so that I can manually drag a cell’s contents into another part of the table? In the table I have built, each user has one entry per row, but they should be allowed to drag that cell to another column in the same row. As the code stands, the only way to do this is to delete the cell’s contents and retype it in the new column, which is not ideal.

Thanks!
Reply
Lucca Avila

November 2018 at 11:56 pm

I really need to know if there is a code that allows to remove the selected object in the multiple selection list. Please, it will be really helpful.
Reply
Dee

November 2018 at 11:21 pm

Do you know if Excel have the function to have fillable group populate after you select from a selection list?
Reply
Vish

November 2018 at 11:41 pm

Thanks Sumit for this excellent trick. This worked like a charm. Can you please advise, how can I now count the number of selections made?
Reply
Stuart

November 2018 at 7:55 pm

I just finished using your code for reoccurring choices. Thanks, it works great. My next task is to work on exporting it to an Access DB.
Reply
Melissa

October 2018 at 12:50 am

First, Thank you. This is extremely helpful and it worked! I just have one issue. It changed my date format. My list has a date format of mmm-yyyy. When I add this VBA code it allowed multiple selection, but changed the date format from DEC-2018 to 12/01/2018. Is there someplace in this code I can specify the date format mmm-yyyy??
Thank you in advance! Greatly appreciative of the help.
Reply
Akash jagtap

October 2018 at 3:00 pm

How can unselect item from selected?
Reply
Tania

October 2018 at 7:46 am

I have copied your VBA without repetition but it doesn’t seem to work. Note I made two changes – (1) replaced the target address to $B$9 and (2) the spacing between selections from “,” to vbNewLine.
Can you please give me ideas why this doesn not seem to work?
Reply
Suri

October 2018 at 7:39 pm

It worked like a charm, instruction and layout is very clean and clear. thank you so much!
Reply
Belinda

October 2018 at 5:17 am

Hi I have just built my spreadsheet which is perfect. Now I need to make one of my drop downs editable. When you put other and want to put in an explanation.
Reply
Jean

October 2018 at 1:03 am

I’d like to alpha sort the multi pick list results so that the list displays alphabetically, not in the order it was selected. Any suggestions?
Reply
Jenny

October 2018 at 9:27 pm

Hi, Thanks for this helpful guide. I copied the VB script and changed the column from C to P and it worked – however I needed to create multiple drop-downs in the entire column ‘P’ rather than C – when I followed the instructions given for this is only worked on column C – and I could not see what I needed to change to make it point to column P. Please can you advise what I need to change in the code to make this work. Many thanks,
Jenny
Reply
- Jenny
  
  October 2018 at 10:14 pm
  
  So I figured this out now – I just needed to replace 3 with 16. However, I can see that there is a problem trying to deselect an item. I see other queries about this – is there a way to do that please.
  Reply
Nahla

September 2018 at 4:29 am

Dear Sumit, thanks for the great code,it works fine with me however when i send the excel file to my college they couldn’t open the file they had the following warning ( this message had contained attached files that deleted during attachment filtering .xlsm) do you have an answer that could help me please
Reply
Deep

September 2018 at 1:05 pm

How can we delete only one selected value form multiple Drop down option
Reply
- Hyperion135
  
  October 2018 at 6:51 pm
  
  Try this:
  
  If InStr(1, Oldvalue, Newvalue) = 0 Then ‘ Add
  Target.Value = Oldvalue & vbNewLine & Newvalue
  Else ‘ Remove
  If InStr(2, Oldvalue, Newvalue) = 0 Then ‘ 1st entry
  If Oldvalue = Newvalue Then
  Target.Value = “”
  Else
  Target.Value = Replace(Oldvalue, Newvalue & vbNewLine, “”)
  End If
  Else ‘ Not 1st entry
  Target.Value = Replace(Oldvalue, vbNewLine & Newvalue, “”)
  End If
  End If
  Reply
  - OscarMD
    
    November 2018 at 12:59 am
    
    Thx Hyperion135:
    had to make a few changes to your code.
    Here is what your final code looks like inserted in the original “without repetition code” provided by Sumit Bansal.
    Works like a charm!
    ‘ To allow multiple selections in a Drop Down List in Excel (without repetition & delete selected value from dropdown list function)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Column = 6 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 InStr(1, Oldvalue, Newvalue) = 0 Then
    Target.Value = Oldvalue & vbNewLine & Newvalue
    Else:
    If Oldvalue = Newvalue Then
    Target.Value = “”
    Else:
    Target.Value = Replace(Oldvalue, Newvalue & vbNewLine, “”)
    End If
    End If
    End If
    End If
    End If
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub
    Reply
Dipti

September 2018 at 12:52 pm

I need to use Multiple select drop down for Marge Column Example :–
I Have Marge C2 , B2 , D2
What address do i need to provide
Reply
Hollie

September 2018 at 8:14 pm

I have managed to get each selection to be on a separate line within the same cell, however when using that cell to place on another sheet within the same workbook the two are pushed together again. Is it possible to adjust this?
Reply
- Jose
  
  January 2019 at 8:23 am
  
  Facing the same issue if you know the answer please let me know.
  Reply
- Theo
  
  August 2019 at 4:17 pm
  
  Hi Hollie, how did you manage to get it on a separate line in the same cell? I really need to do this but I can’t figure out how.
  Reply
Lucy

September 2018 at 8:38 pm

How do you deselect an item? I used the code without repetition, but now I want to deselect and item, but nothing happens and I can not delete the item. For example I select one, two from drop down list. Now I do not want two, how do I get rid of it? Thank you.
Reply
Gary

September 2018 at 1:11 am

Hi there, this doesn’t work when I protect the worksheet. I am using this code to make a rudimentary form, so I want to protect the other cells from editing. The cells that contain the dropdown are not protected but the vba still doesn’t work. As soon as I protect the worksheet the drop downs only allow a single selection. What can I do to make this work. My apologies if this is answered somewhere and I didn’t see it.

Thanks for the great site and sharing your Knowledge!
Reply
Hayden

September 2018 at 9:47 pm

This works great. I am having a problem counting the selections now. I have a separate count section that every time I select “white” in a column, it will then count those occurrences in another section. Now when I select “white, Black, native” the count doesn’t work. How do I fix this issue? Basically, I want to be able to select multiple options in a drop down and have them be counted separately. The code I use for the count is =COUNTIF(C:C, “BLACK,”)
Reply
KS

September 2018 at 7:29 am

This worked and was so easy! Thank you so much!
Reply
Trena

September 2018 at 12:33 am

I’ve managed to do everything but my multiple choices still kicks out the previous choice. I have gone to address and changed it to column and on $c$2 to 4 as I want the whole column.
I’m doing this for a non for profit service dog trainers so we r not techies. I thought I was following the directions is my software or system too old? It really need this little thing it could change our who training program. Thank you in advance I feel you were very easy to follow but I am missing something.
Reply
JMB

September 2018 at 5:52 pm

Hi Sumit – your VBA instructions are great and I love that you’ve thought about all the other uses (ie duplicates / non duplicates, separate line in cell) as it’s perfect for what I want! I’ve done some tests which all work fine and will add the code to my working spreadsheet. Fingers crossed!
Reply
Pearlyn

September 2018 at 2:58 pm

I noticed that after adding the codes that allows me to select item once, I can’t delete the last input if it was incorrectly selected. It will give me validation error message. How do I counter this issue?
Reply
SG

September 2018 at 9:05 pm

can I applied this for other columns in the same worksheet?
Reply
BMRHON

September 2018 at 3:07 am

You are brilliant! thank you very much for posting this. It seems to work great!!
Reply
Karina

September 2018 at 12:34 am

Thank you for this code, works great. How do you deselect an item? I used the code that does not allow selection of the same item but now I want to deselect but nothing happens and i can not delete the item. For example I select one, two from drop down list. Now i do not want two, how do i get rid of it. Thank you.
Reply
- sarah
  
  September 2018 at 2:01 pm
  
  I also want to know this
  Reply
Jared

August 2018 at 11:41 pm

How do I alter this code so that there is an ending row for the multiple selections of the drop-down list?

For example, I have a table on excel with a drop-down list in columns 2 and 3 (B and C). I would like to alter this function so that at the bottom of this table, the function stops. I would also like to be able to use functions, such as count in column 4 which is D, on excel.

All help is appreciated, thanks so much!
Reply
Tracy

August 2018 at 3:28 am

I cannot make it work. I downloaded the file to see what the problem was and I cannot make it work with that either. I can still only have one selection from my dropdown box. What am i doing wrong?
Reply
Brent

August 2018 at 5:56 am

Thanks for the tutorial but I think I must have missed something.

I put information for drop down list in Sheet 2.
Sheet 1 – created drop down list and targetted information in Sheet 2.

Pressed Alt-F11 and got the Visual Basic for Applications (VBA) screen, selected Sheet 1, pasted the code and closed VBA.

Tried to select more than one item from drop down list and only got one item showing at a time.

Reopened VBA, deleted code from Sheet 1 and copied the code to Sheet 2 then closed VBA.

Tried to select more than one item from drop down list and only got one item showing at a time.

Not sure if need to have a particular cell or sheet selected before Alt F11?

Using Microsoft Office 365 ProPlus.

Cheers.
Reply
ken

August 2018 at 1:57 am

How can I get the code to choose items in a dependent dropdown list? Suppose I have a different list in each dropdown?
Reply
Nick

August 2018 at 12:12 am

Thank you. Hope this will help this old man learn and understand excel
Reply
Mase

August 2018 at 7:30 am

I copied and pasted the code and it works!!!

now, how do you sumifs multiple values from multiple drop down list using named ranges ?
i.e. suming multiple product groupings as well as multiple regions .
Reply
Mase

August 2018 at 7:30 am

I copied and pasted the code and it works!!!

now, how do you sumifs multiple values from multiple drop down list using named ranges ?
i.e. suming multiple product groupings as well as multiple regions .
Reply
Karen Behrns

August 2018 at 6:29 pm

Hi, How can i get this to apply to multiple worksheets?
Reply

Leave a Comment Cancel reply