Search Contextures Sites
Excel Data Validation - Select Multiple Items
Select Items With Drop Down List
Select Multiple Items From Drop Down List
Excel VBA to Select Multiple Items
Download the Sample Data Validation File
Video Multiple Item Selection Examples
Video: Multiple Item Selection Setup
Allow Editing With Multiple Selections
Remove Previous Selections From Cell
More Data Validation Tutorials
Download the zipped sample file: Select Multiple Items from Excel Data Validation List
To make data entry easier, you can add an Excel data validation drop down list to your worksheet. Then, click the arrow, and select an item to fill the cell.
In the worksheet shown below, you can select any month from the drop down list, and it overwrites any previous entry in the cell.
Instead of limiting the drop down list to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
With a few adjustments to the VBA code, you can display all the selected items across a row, or down a column, or keep them in a single cell.
Fill Across a Row
In the first example, each item selected in this drop down list fills the next empty cell at the right.
With this option, you could fill player names across a list of innings, or employee names for days of the week.
Fill Down a Column
Another option is to fill the selected items down a column, instead of across the row.
Add Values To the Same Cell
With another variation in the VBA code, you can keep all the selected items in the same cell, separated by commas.
The sample workbook uses Excel VBA code, which is set up to run automatically, when you make a change on the worksheet.
To see the code for the SameCell sheet, you can right-click the sheet tab, and click on View Code.
In the sample code shown below, column 3 is the only one where the change will occur.
In your workbook, you could change the 3 to a different number.If Target.Column = 6 Then
Or, add more columns in the code. For example:If Target.Column = 3 _ Or Target.Column = 5 _ Or Target.Column = 6 Then
Or, if you donít want to limit the code to a specific column, you could delete the two rows that are marked with a red circle.
To experiment with this technique, you can download the zipped sample file: Select Multiple Items from Excel Data Validation List
To see these techniques, and a few other multiple selection examples, you can watch this short video.
To see the steps for setting up data validation with multiple selection, watch this short video tutorial
With this technique, you can select multiple items from a drop down list, but if you try to edit the cell, you might see a data validation error alert, or find that the VBA code simply added your revised text, to the existing text -- probably not the result that you wanted!
Edit the Multiple Selection Drop Down List
In the sample file, on the SameCellEdit worksheet, there is an "Edit Entries" check box. Check that box, and you can edit the cell, without any problems.
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.
Add an Editing Check Box
To add a check box on your worksheet:
- 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.
The Revised Multiple Selection VBA Code
In the sample file, you can see the code that allows editing when the Edit Entries box is checked. In the screen shot shown below, the EditMode range is set as a variable (rngEdit), and the code looks at that cell's value.
- 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.
On the SameCellAddRemove worksheet, there is an example that lets you remove items if they been previously selected. For example, the cell contains four items -- "One, Two, Three, Four".
When you select Three again, that number is removed from the cell, instead of being added at the end of the cell.
Video: Remove Previous Selections From Cell
This video shows how the multiple select and remove features work. You can also see the Excel VBA code, and the steps that it goes through, when you make a selection in the drop down list.
Excel Tutorials - Data Validation
Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List
Data Validation - Dependent Lists With INDEX
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: May 19, 2013