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”

Mihai

May 2020 at 4:59 pm

Hi,

I need help modifying the code.
I want to appear “Please select” in the Target.Address/Target.Column.
I tried but i failed. 🙁
Reply
P.Alouidor

May 2020 at 5:15 am

I love the one with no Repetition but it has a flaw I need help with. I disable error and ignore checkbox via the data Validation this way it give me the option to have a drop down with multiple selections but also the ability to still add my comments so to speak it then repeats the selected selections. The next issue is that when I select 2 options in my drop down list and then try to delete one it then duplicate and add them back to speak. Are we able to get one where I have be able to delete one of my selection if I no longer decide to have multiple selection with it then repeating. Same goes for when I want to add my own comments that is not on the drop down list source. I I’m basically looking for a drop down list to be able to pick multiple options and still be able to add my own comments in the same field with out causing any errors or it duplicating my selected options and be able to delete.
Reply
Stacy M Mitchell

May 2020 at 12:56 am

I inserted the code so that the checklist would appear in every cell in column E but it didn’t work. I made the change:

If Target.Address = “$C$2” Then

with this line:

If Target.Column = 5 Then

My validation list is on another tab. Is that the problem?
Reply
Andrea

April 2020 at 2:10 pm

Hi Sumit, i have inserted the code for multiple selections (without repetition) within a column for multiple sheets (same template) but it does not work. Not sure if the cause is conditional formatting of the options (which I’ve coloured) or if it’s something else? Appreciate your help here, thank you!
Reply
MJA

April 2020 at 4:20 pm

How do I create multiple selections in multiple columns in the same file? Right now, I can only get the code to apply to one column at a time.
Reply
Mohtashim

April 2020 at 7:23 am

Hi Sumit,

Thank you for your great assistance in multiple selection drop down list through a VBA code. I have used it and it is working fine for me. Only, change in the output I need per my project’s requirement is, can we have the selections in either new rows or columns rather new line? It would be a really helpful if that is possible.
Reply
Goyo

April 2020 at 9:33 pm

I copied and pasted your code. It does not work when I use the target.colum line. see below:

If Target.Column = 3 Then

The above code does not work unless I put I write with with quotation marks –>

If Target.Column = “3” Then

Now I can choose multiple items, however, I get a Data verification error. When I use the debugger it points to:

Application.EnableEvents = True
Reply
Estelle Crowley

April 2020 at 9:13 pm

Can I do this for a whole workbook?
Reply
Elston Hynd

April 2020 at 5:16 pm

I have successfully created a multiple drop-down list without repetition – thank you. However, when I apply the “Me.Protect UserInterfaceOnly:=True” code, so I can use it when the sheet is protected, it doesn’t hold its password protection and by simply clicking un-protect the spreadsheet becomes exposed. Do I need more code?
Reply
SG

April 2020 at 3:58 am

This was awesome, thank you! How do I reset a list where selections have been made 🙂 My list is LARGE – 123 unique values
Reply
Dj

April 2020 at 11:19 pm

Hi Sumit,

Your code is a life saver, Thank you. It saved a lot of re-work for me.
However in my case, the data is getting duplicated in case I am selecting one item multiple times. Any suggestions?
Excel Version: MS Excel for Office 365 MSO (16.0.11929.20708) 64-bit

List of values:
One
Two
Three
Four
Five
Output (upon multiple selection):
One, Two, Five, One

Regards,
Dj
Reply
Spaghetti-o

April 2020 at 4:19 pm

Hello, this was spectacular thank you for creating.
The code you provided to enable the code while protected is great – HOWEVER it is constantly protected. I cannot switch it off? Everytime I make an adjustment to a cell, I have to unlock it again. How do I make it so that it looks ONLY when I switch on protection? Thank you.
Reply
Sushil G

April 2020 at 8:26 pm

If you want to add more than 2 column then place column numbers in below formula. I did it from column no.9 to no. 20. If and then should be used in start and end only.

If Target.Column = 9 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 12 Or Target.Column = 13 Or

Target.Column = 14 Or Target.Column = 15 Or Target.Column = 16 Or Target.Column = 17 Or Target.Column = 18 Or

Target.Column = 19 Or Target.Column = 20 Then
Reply
Jimmy

April 2020 at 4:23 pm

Hi.. thanks a lot for sharing this, it’s really helpful. But is there a way to limit the amount of selections one can make in a drop down list to say, 5? Currently there’s roughly ~25 items in the list and I only want them to be able to select a maximum of 5 of those at a time. Thanks you.
Reply
Alice

April 2020 at 4:28 am

Hi,
Thank you for this very comprehensive tutorial!
I wanted to know if I can have different multiple selection drop-down menus for different columns. I explain: on a worksheet named Services provided I have in column B a drop-down menu for target clients; in column C a drop-down menu for basic services and in column F a drop-down menu for Specific areas of expertise… how can I do this?
I would really appreciate you explaining it to me!
Thanks
Alice
Reply
Petz

April 2020 at 8:22 am

Very useful information and easy to follow. Thank you very much.
Reply
Mike McClure

April 2020 at 11:53 pm

Sumit,
Thank you very much for this tutorial. However I have a question not covered by those answered above. I would like to have the same ability in two columns. When I change your code from $C$2 to column. “38” I also want the same ability in column 40. I tried Columns. (“38 & 40”) in the command, however neither column will allow multiple drop down selection. Yours is the very first macro I’ve worked with so I know syntax is crucial. Can you offer any help.
Thank you
Mike McClure
Reply
Laurel Langille

April 2020 at 2:25 am

Can I modify multiple selection code (no repetition) when Target.Column = 6 so that the header is not affected? Currently, the Column F header is following the “separated by a comma” rule with any edits to the header text in Cell $F$1.
Reply
Meghan Khater

April 2020 at 9:28 pm

Hello and thank you for this amazing presentation! One question- how would i modify this to do multi-selects in multiple columns? For example, if i have a set of drop downs in columns B,C, D with unique selections, how do i modify the code to capture this functionality for multiple columns? best.
Reply
Jenny

April 2020 at 7:14 pm

Is it possible to apply this code to an entire column?
Reply
- Tanya
  
  April 2020 at 3:38 pm
  
  Hi! I was wondering the same with specific columns and i did it like this and it worked, hope you can get some help of this: If Target.Column= 2 or Target.Column = 4 Then
  Reply
HECTOR GARCIA

April 2020 at 12:35 pm

How can you edit the text in the cell once selected? I tried to remove a line of text and i get an error each time.
Reply
Jon

April 2020 at 12:01 am

When the sheet is protected then the ability to add more than one selection. For example, I can select one item and it will replace whatever previous item was there instead of adding it to a comma-separated list.

Thoughts?
Reply
Cody Griffin

April 2020 at 3:54 pm

So, I just found this page, and am trying to get this to work. Currently, my drop down list text is located in Sheet 2, but the drop down box itself is in Sheet 1. How would I get this to work?
Reply
dave williams

April 2020 at 7:15 am

This is great, works well, many thanks
Reply
Please help

April 2020 at 6:52 am

sir how can i create 2 rules (multiple dropdown selection) in two or more different column?
Reply
kishori

April 2020 at 6:33 pm

Hello, Sir, I have tried this option for my excel report for SQL database parameters to pass multiple values for one parameter but my report showing blank when I select multiple values .is there any option for this please suggest
Reply
yaya

April 2020 at 6:39 pm

Thanks a lot, very clear, good programmer
Reply
Sacha

April 2020 at 5:53 am

Real well explained, I finally found my solution. thank you very much for explaininf and giving simple code for each cases
Reply
Tobias

March 2020 at 8:48 am

Thank you so, so, so much! This helped me a lot with some work related issues!
Reply
Lokesh

March 2020 at 3:04 pm

Hi,
Can I remove existing selection by clicking on the same value in drop down list.
Reply
Louise

March 2020 at 3:35 pm

The spreadsheet works when I save however this is contained within a shared folder so when my colleague accesses the code no longer works. How do I fix this?
Reply
Andrew David Jones

February 2020 at 4:19 pm

Thank you Sumit Bansal. Question: I have another column in the same sheet I would like to put a multi select drop down list into. How can I do this? I have tried changing the Oldvalue & Newvalue to Oldvalue 4 and Newvalue4 for the additional column but it does not work. Do i need to set a range in this line “If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then” ?
Thank you
Reply
rene lovato

February 2020 at 4:35 am

I tried emailing my spreadsheet with the added code but when they get the file the code in not there. what happens to the code when I email the excel file to someone?
Reply
Nicki Sweeney

February 2020 at 3:17 pm

Why can’t colleagues see the multiple selections in a drop down list? It’s the same document on a shared server.
Reply
Priyanka

February 2020 at 1:07 pm

As of now, the multiple selections are separated by a comma. How can I change this to separate and show in in different columns?
Reply
Jebin Davidson

February 2020 at 12:00 pm

Awesome Man!!!!!
Reply
Andrew Watson

February 2020 at 7:25 pm

Hi. Thank you for this guidance it is excellent and very easy to follow. I have read all of the comments and there are many unanswered questions about subsequent filtering of data using the multi select cell information. I have encountered this issue too. Can you help? Is there a way around this? Basically I have multiple filters informed by the drop down. Where a single selection is made the filter finds it. Where multi selection is made the filter cannot since there is no exact representative value in the data (e.g. Multi select shows East, South but the data column contains East). I guess I need the filter to recognise and search for each individual selection separately. Is this possible? Would be extremely grateful for advice. Thanks in advance.
Reply
RAFIZAH BINTI SABUDIN

February 2020 at 10:22 am

Hi,

What if the target at different worksheet?
Tq
Reply
LoveExcel

February 2020 at 2:18 am

Thank you so much! I was using the macro way which is 10x complicated than this. This is a genius method.
Reply
Deb

February 2020 at 7:01 pm

Thank you so much! Your posts have got me out of sticky situations more than once!
Reply
CHARLES KIRKWOOD

February 2020 at 5:20 am

OOPS.. had to fix the if statement to handle rows higher than 9

If Target.EntireColumn.Address = “$G:$G” And Mid(Target.Address, 4, Len(Target.Address) – 3) > 1 Then
Reply
CHARLES KIRKWOOD

February 2020 at 4:15 am

this is EXACTLY what I was looking for, thank you!
My contribution to this: For those looking to apply the multi-select functionality to a range of cells… just replace the IF statement with this:

(assuming you want this validation rule to apply to the range of cells c2 = c10)

If Left(Target.Address, 2) = “$C” And Mid(Target.Address, 4, 1) > 1 And Mid(Target.Address, 4, 1) < 11Then

I just tested this out successfully in my spreadsheet
Reply
- CHARLES KIRKWOOD
  
  February 2020 at 5:22 am
  
  oops – had to fix for rows past row 9, also modified to col G
  
  If Target.EntireColumn.Address = “$G:$G” And Mid(Target.Address, 4, Len(Target.Address) – 3) > 1 Then
  Reply
  - LoveExcel
    
    February 2020 at 2:17 am
    
    I am going to apply your code but would like to thank you first!
    Reply
  - sumit
    
    February 2020 at 5:55 pm
    
    Hi Charles, I am getting a syntax error when using this
    Reply
- LoveExcel
  
  February 2020 at 2:33 am
  
  Btw, be aware of the double quote!
  Reply
Amit Nambiar

February 2020 at 1:03 pm

If you want to apply this for all data vallidation on the sheet then ?
Reply
- CHARLES KIRKWOOD
  
  February 2020 at 4:15 am
  
  Amit see my answer above ‘multi-select functionality
  to a range of cells’
  Reply
Kimberly Frankel

February 2020 at 12:14 am

You have made my day with all of your “How To” directions on this site! Allowing for multiple selections from a drop down box is great! Is there a way to make this a bit easier by making all of my selections at once?
Reply
Ish

January 2020 at 4:33 pm

How can i delete items from the list?in the above program if i want modify then i have to delete everything and select once again is there any method to just delete required items?
Reply
Srikant Bajaj

January 2020 at 10:02 pm

This is excellent and really useful. I have one question though.. if I select multiple items from drop down and want to get back again to single or lesser item? How do we do that?
Reply
Vivitha

January 2020 at 2:46 pm

Hi,

In the multiple drop down I need each item selected to be numbered as (1), (2) etc..
Can someone help me by providing the VBA code for the same
Reply
Fadi joria

January 2020 at 4:39 pm

I need to create multiple drop-downs in a range of cells example m4:m5000 . How can I do this?
Reply
- CHARLES KIRKWOOD
  
  February 2020 at 4:16 am
  
  see my comment on ‘multi-select on a range of cells’
  Reply
Brian Keegan

January 2020 at 7:30 pm

Q: if I wanted to run a Countif function would it recognize each selection separately or think it was one long value?
Reply
Riva Khanna

January 2020 at 8:36 pm

Hello, Could you please help me if I want to one dropdown in column 4 another in column 5, another in 6 and so on.
Please be kind to help
Best Regards
Reply

Leave a Comment Cancel reply