Edit Multiple Selections in Excel Drop Down

Edit Multiple Selections in Excel Drop Down

A couple of years ago, I described how you could select multiple items from an Excel drop down list. One of my clients needed that feature in a workbook last week, so I've made an enhancement to the VBA code. Now you can edit multiple selections in Excel after entering them.

Problems With Editing

In the previous version, you could select multiple items, as promised, with no problems. However, if you tried to edit the cell, you might have seen the data validation error alert.


To prevent that error alert from appearing, you can turn off the setting.


However, the helpful VBA code simply added your revised text, to the existing text. That's probably not the result that you wanted!


Add an Editing Check Box

In the new version, there is a check box on the worksheet. If you add a check mark, you can edit the cell, without any problems.


To add the check box:

On the Excel Ribbon's Developer tab, click Insert. Next, click the Check Box under Form Controls


Click at the top of the worksheet, to add a check box


Select the default text, and type "Edit Entries"


Somewhere in the workbook, create a cell named "EditMode". In this example, the EditMode cell is on a worksheet named AdminNotes.


Right-click on the check box, and click Format Control


On the Control tab, enter EditMode as the Cell link, and click OK.


Edit Multiple Selections in Excel Drop Down List

To edit a cell with multiple items selected:

  • Add a check mark in the Edit Entries check box
  • Select the cell, and edit the entries, either in the cell, or in the formula bar

edit multiple selections in Excel

  • Press Enter, to complete the editing.

Return to Multiple Selection

After you finish editing the cell(s), press Enter, to complete the editing.
Then, remove the check mark from the Edit Entries check box.


The Revised Multiple Selection VBA Code

In the revised code, the EditMode range is set as a variable, and that cell's value is checked.

  • If the value is False (no check mark), the multiple selection code runs.
  • If the value is True (check mark), the multiple selection code does not run.


To see the full code to edit multiple selections in Excel, download the sample file from the link below.

Then, go to the SameCellEdit sheet, right-click on the sheet tab, and click View Code.

data validation multi select edit

More Articles on Select Multiple Items

Please read these articles, for more details on selecting multiple items from an Excel drop down list, and working with the VBA code:

Download the Sample Data Validation File

To try this technique to edit multiple selections in Excel, you can download the zipped sample file from the Contextures website.

Click here to go to the Excel Data Validation - Select Multiple Items page. This example is on the SameCellEdit sheet.

Buy the Premium Version

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. Instead of selecting from the drop down, a list box appears when you click the cell.

It automatically selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.

The premium version works with dependent lists too, and runs on a protected worksheet. There is also an option of showing a multi-select listbox, or a single-select version. Single-select is helpful when working with dependent lists -- you don't want multiple items selected in the main columns.

The kit has 3 Excel files, and a user guide, with details and screen shots, on how to add this technique to your own workbooks. Click here for details: Data Validation Multi Select Premium.

0 thoughts on “Edit Multiple Selections in Excel Drop Down”

  1. [...] Edit Multiple Selections in Excel Drop Down Lists [...]

  2. Hi --
    I love the idea of what you have created, but am a struggling newbie! I used an earlier version where the Edit checkbox had to be used to modify previous multi-entries. It worked great on my PC, but I had attached the code to an Excel tool I delevoped for another department...I've never been able to get it to work for them. ๐Ÿ™
    Now I switched to your new code that permits a click to select/de-select and I can't even get it to run on my own version of the spreadsheet. In the original it was obvious what I needed to do to have the code look at the 3 different columns where I had the select lists. In the new code, it looks like I would change this:
    Which was previously a , but my data are in column 7.
    However, no luck. Can you point me to a resource that will help me to determine what I'm doing wrong (or explain it to me like I'm a 3rd grader)? I've been reading blogs and forums for 2 days to try to educate myself so that I know where to look for my error, but to no avail. I've tried to get back to your earlier postings, hoping for more detail, but the hyperlinks don't find the content. ๐Ÿ™
    Thanks so much for your wonderful blog and great ideas!!

  3. Ooops! Guess it doesn't like entries in angle brackets!
    The section of code referred to above:
    If Target.Column = 7 Then
    And the 7 was a 3 in your original code!
    Sorry... ๐Ÿ™‚

  4. Hi
    Drop down spreading across columns works great, is there a way to restrict it to only one drop down list rather than the whole sheet ??

  5. Thank you very much for the code. I am trying to help a person who cannot handle a database but is familiar with Excel.
    I had to remark out the code:
    'If rngDV Is Nothing Then GoTo exitHandler
    'If Intersect(Target, rngDV) Is Nothing Then
    'do nothing
    as if I left this in rngDV always returned "Nothing" and just jumped to the exit handler.
    I am using Excel 2002 and the ENTIRE column is set to do validation.
    I need to allow editing of the cell but as this portion of the code is not working as expected I am unsure as to how far to go.
    Can someone at least give me a clue as why rngDV is returning "Nothing"
    Thanks again!

  6. Hi,
    Quick question on this - creating the drop-down list allowing multiple selections worked for me. However, I'm trying to put this into a spreadsheet that needs to be filtered though for a specific selection. When using the standard excel data filtering tool it doesn't seem to work correctly once you've changed the column into a multi-select.
    For example:
    Drop-down is multi-select and has the following possible selections: One, Two, Three, Four
    When filtering for all cells that have selection "Two" in them, it only gives you the cells that have ONLY option "Two", not all cells that include selection "Two" (ie: a cell that could be "Two, Three" or "Two, Four".
    Is there any way to have the filter / sorting work on a multi-select drop-down?
    Thank you!

  7. Are you able to provide the full VBA code for the Edit Entries check box please. Mine doesn't seem to be working without it.

  8. I can't get this to work for the life of me. Is there code that can be provided for editing the entries instead? Looks like the same thing as Phoebe's comment above.

    1. @KK the code is in the sample file that you can download, from the link near the end of the article above.
      After you get the file, go to the sheet named SameCellEdit, right-click the sheet tab, and click View Code.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.