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.
DataValMultiEdit01
To prevent that error alert from appearing, you can turn off the setting.
DataValMultiEdit02
However, the helpful VBA code simply added your revised text, to the existing text. That’s probably not the result that you wanted!
DataValMultiEdit03

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

To add the check box:

On the Excel Ribbon’s Developer tab, click Insert. Next, click the Check Box under Form Controls
DataValMultiEdit04
Click at the top of the worksheet, to add a check box
DataValMultiEdit05
Select the default text, and type “Edit Entries”
DataValMultiEdit06
Somewhere in the workbook, create a cell named “EditMode”. In this example, the EditMode cell is on a worksheet named AdminNotes.
DataValMultiEdit07
Right-click on the check box, and click Format Control
DataValMultiEdit08
On the Control tab, enter EditMode as the Cell link, and click OK.
DataValMultiEdit09

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

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.

DataValMultiEdit12
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 sample 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.
_____________