Edit Multiple Selections in Excel Drop Down Lists

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.

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 -- 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, and then 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 the Multiple Selection 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

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

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:

• examples of selecting multiple items from a data validation drop down list
• how to set up multiple selection
• prevent duplicates in multiple selection column
• select or remove multiple items from an Excel drop down list

Download the Sample Data Validation File

To experiment with this technique, you can download the zipped sample file from the Contextures website, on the Excel Data Validation - Select Multiple Items page.

_____________