Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Excel Data Validation - Select Multiple Items

Video Multiple Item Selection Examples

To see how this technique works, and a few multiple selection examples, you can watch this short video.

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.

Data Validation Drop Down

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.

Data Validation Fill Across

Fill Down a Column

Another option is to fill the selected items down a column, instead of across the row.

Data Validation Fill Down

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.

Data Validation Fill Same Cell

View the Code for 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 any sheet in the sample file, you can right-click the sheet tab, and click on View Code.

Sheet Tab View Code

Change the Column Number

In the sample code shown below, column 3 is the only one where the change will occur. In the screen shot, the line is marked with a red dot:

If Target.Column = 3 Then

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 dot. When those lines are removed, the code will run when any cell with a drop down list is selected.

Data Validation Multiple Select Code

Change the Sheet and Range Names

In some code samples, the sheet names and named ranges are included. You will have to change these to match your sheet names and range names.

For example, shown below is a portion of the code from the LineBreakAddSort sheet. The sheet and range names are highlighted in yellow.

Also, this code is set up for a list in column 3.

i=ws.Cells(Rows.Count,3).End(xlUp).Row + 1

You would need to change that number, if your list is in a different column.

Data Validation Multiple Select Code

Workbook Open Code

In the workbook, there is code that automatically runs, when the workbook is opened. That code is in the ThisWorkbook module, and it protects the sheet named "SameCell". You can delete that code, if you don't want to use it.

Data Validation Multiple Select Code

NOTE: If you delete the "SameCell" sheet, and do not delete the Workbook_Open code, you will see an error message when you open the workbook. That error appears because the code can't find an object that is mentioned in the code (the SameCell sheet).

Data Validation Multiple Select Code

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!

DataValMultiEdit03

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

DataValMultiEdit10

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

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

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

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.

DataValMultiEdit12

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

datavalmulticellremove01

When you select Three again, that number is removed from the cell, instead of being added at the end of the cell.

datavalmulticellremove02

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.

Multiple Selection Sample Code

The following code samples should be copied onto the worksheet module where you want to use the multiple selection technique. This code adds multiple items in the same cell, separated by a comma.

NOTE: This code does not work if the sheet is protected.

Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 3 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      Target.Value = oldVal _
        & ", " & newVal
'      NOTE: you can use a line break,
'      instead of a comma
'      Target.Value = oldVal _
'        & Chr(10) & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

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

NOTE: The sample file contains links to the tutorial videos on YouTube. You might be asked to enable content to run, so click OK, if you want to watch the video within the sample file.

If you copy the code into a different workbook, remember to check the code for sheet names, range names, row numbers and column numbers, and adjust those if necessary.

Buy the Premium Version

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium

In the premium version, the list box 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.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

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

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright ©2014
All rights reserved.

 

Last updated: August 10, 2014