Instead of limiting a data validation drop down list to a single selection, you can use this macro to allow multiple selections. Video and written instructions, and get the free workbook to see how it works.
This is the main page for the data validation multiple selection technique. I have also written several blog articles, and you can join in the discussion in the comments there:
To see how this technique works, and a few multiple selection examples, you can watch this short video.
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.
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.
Another option is to fill the selected items down a column, instead of across the row.
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 any sheet in the sample file, 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 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, use a starting and ending column. For example:
If Target.Column >= 3 And _ Target.Column <= 10 Then
Or, include a starting row as well. For example:
If Target.Column = 3 _ And Target.Row >= 4 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.
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.
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!
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:
Press Enter, to complete the editing
After you finish editing the cell(s), press Enter, to complete the editing. Then, remove the check mark from the Edit Entries check box.
To add a check box on your worksheet:
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.
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.
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.
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
If the worksheet is protected, the Cells.SpecialCells(xlCellTypeAllValidation) code will not work. Instead, you can check the .Validation.Type property of the target cell, to see if it contains a drop down list.
This example is on the SameCellProtect sheet in the sample file. It is set up to work on column 3 only.
NOTE: The Premium Version also works on protected worksheets.
Private Sub Worksheet_Change(ByVal Target As Range) ' Developed by Contextures Inc. ' www.contextures.com 'code runs on protected sheet Dim oldVal As String Dim newVal As String Dim strSep As String Dim strType As Long 'add comma and space between items strSep = ", " If Target.Count > 1 Then GoTo exitHandler 'checks validation type of target cell 'type 3 is a drop down list On Error Resume Next strType = Target.Validation.Type If Target.Column = 3 And strType = 3 Then Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value If oldVal = "" Or newVal = "" Then Target.Value = newVal Else Target.Value = oldVal _ & strSep & newVal End If End If exitHandler: Application.EnableEvents = True End Sub
If you enter multiple items in a cell, you will see each unique combination of items in the AutoFilter drop downs. That can make it difficult to filter for a specific item, such as "Two".
In Excel 2010 and later, type in the filter Search box, just above the list of filter options. The list is automatically filtered as you type, to show only the items that contain the text that has been entered in the Search box..
In Excel 2007, where there is no Search box, use the Text Filters command. Then, click the Contains command, to open the Custom AutoFilter box, and enter the text you want to filter.
Read more about filtering multiple selection on my Contextures Blog: AutoFilter For Multiple Selections
To experiment with this technique, you can download the zipped sample file: Select Multiple Items from Excel Data Validation List
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.
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.
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 Contextures Sites
Last updated: December 5, 2016 12:00 PM