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.
_________________
I am using Excel 2007 and can get the code to work, however when I save the file I get a msg indicating I cannot save the following features: VBA Project, in a “macro-free” workbook. I gives me an option to save as “macro-enabled” workbook however the file type changes to XLSM. Is that the correct way? Will that work for users that have Excel 2003? Thanks…r
@Robert, you can save the file as Excel 97-2003 format, and people with Excel 2003 will be able to use the file too.
I am using the SameCell code and it works but I need to sort the completed input within the cell, so instead of getting orange, apple, pear; I need to see apple, orange, pear regardless of the order in which the user selects the item. Can you help? Thanks!
I am using the following code to select multiple items from a data validation cell. It works but I need to sort the entries within the cell so that instead of getting pears, apples, oranges, the result would be apples, oranges, pears, regardless of the order in which the user selects from the dropdown list.
It would also be nice (but not necessary) to check for duplicates.
Can someone PLEASE help? I need urgently. Thanks!
Option Explicit
‘ Developed by Contextures Inc.
Private Sub Worksheet_Change(ByVal Target As Range)
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
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Hi there, thanks for this tutorial is really helpfull and I apreciate you’re sharing it with us, but I just have a question. I have a table that uses macros to add a new dropdown list each time you click on a button, I have at first four lists to chose multiple products but then I have to add more dropdown lists with the button, the problem is that the new dropdown lists don’t have the same ability as the others of selecting multiple choises in the same cell.
How can I keep the multiple selections each time I add a new list with the button?
Greetings from Mexico, Thanks
Hi, I am trying to duplicate step by step your “Select Multiple Items from Excel Data Validation List” using Same Cell.
I downloaded you DataValMultiSelect spreadsheet thought I could just copy and paste your code into a tab on my spreadhseet but cannot do it. Maybe I am not allowed to. That is ok. Is there a way to duplicate this easily. Do I have to pay? That’s ok too. I am running Excel 2007