Contextures - Microsoft Excel Tips and Tutorials

Search Contextures Sites

• Home
• Excel Tips
• Excel Files
• Products
• Blog
• Contact
• RSS

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 Select Items With Drop Down 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. Select Multiple Items From Drop Down List 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. Excel VBA to Select Multiple Items 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. Download the Sample Data Validation File To experiment with this technique, you can download the zipped sample file: Select Multiple Items from Excel Data Validation List Video Multiple Item Selection Examples To see these techniques, and a few other multiple selection examples, you can watch this short video. Video: Multiple Item Selection Setup To see the steps for setting up data validation with multiple selection, watch this short video tutorial Allow Editing With Multiple Selections 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. Remove Previous Selections From Cell 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

Home Excel Tips Excel Files Products Blog Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish

Last updated: May 19, 2013