You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
Hi Debra,
I was able to use your code to allow for the multiple selections and it’s working great except that I get the little green triangle trace error in my cell whenever a 2nd choice is selected. I can individually click to ignore error each time, but is there a way to get this from occurring?
Thank so much!
@Jon, you could turn off all the data checking for tables, if that won’t cause other problems for you. This setting will affect all workbooks that you open.
To change the setting, click the Error Message button when it appears, then click Error Checking Options
In the Error Checking Rules section, remove the check mark for “Data entered in a table is invalid”
Click OK
I am using the option to fill the entries down the column, but I want to use this multiple times on a sheet. When I make my selection on each row (same column) that I have data validation (example: rows 8, 16, 23, 30; all col. D have unique data validation selections), the selections appear in col. E in the same row as the data validation cell, and go down as expected. The problem is that if I go back up and change the entry in D8, the additional selection is put in a row after the last selection (ex: E31) after the last data validation box selection result. How can I make separate areas so that I can have multiple selection cells on a page. I am using this for a questionnaire and need the next question to ‘reset’ so to speak with a new set of responses but still allow the user to go back to the first one and add an item in the right area.
Thanks.
Hi Debra,
I am working on a pricing sheet for my work and I used this code to select multiple items from a drop-down list to be priced. In another cell I need to write a formula that will reference the cell with multiple selections and add the corresponding prices to those selected cells together in the same cell. I have the formula worked out however excel is not recognizing that the list is showing multiple cell selection. Is there an option in this code to allow for a cell to recognize that another cell is validating multiple selections?
Thanks,
Hi Jacob,
There’s nothing in the code that will help another cell the recognize multiple values. You could try to modify the code, so it puts the prices in another cell, when adding the items.
There is a sample file on my website that changes a product name to a product code, after it is selected. That might give you some ideas. It’s on the Sample Files page — look for DV0004 in the Data Validation section:
http://www.contextures.com/excelfiles.html
I created a drop down list that allows multiple selections from the list but once I make my selections, if I want to remove a selection(s) from the cell I get an error message an cannot remove it. The only way I can remove selections I do not want in the cell is to clear the cell completely and re-select. Do you have a solution that will allow just clearing particular selection(s) instead of the entire cell. Here is the code I used to allow multiple selections:.
Private Sub Worksheet_Change(ByVal Target As Range)
‘Updated: 2016/4/12
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 “” Then
If xValue2 “” Then
If xValue1 = xValue2 Or _
InStr(1, xValue1, “, ” & xValue2) Or _
InStr(1, xValue1, xValue2 & “,”) Then
Target.Value = xValue1
Else
Target.Value = xValue1 & “, ” & xValue2
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
Hi Debra!
i have a question, how can i make a drop down list which brings a row of data (6 or 10 columns or may be more), like the example of the fruits and vegetables…
Is it possible to create a drop down list in a cell based on specific selections from other cells?
For example, if we have some values on column A and B, than in column C to have a specific drop down list with 4 values; and if in column A and B we have other values, in column C to have a different drop down list with only 2 elements, e.g.
Many thanks in advance!