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.
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 (this 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.