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.
_________________
Very sweet code. Thank you so much!!!!! Full credit given.
Thank you so much for publishing both the multiple select codes as a zip and doing the video. I am completely unexperienced with programming and this has gotten me so much further than I would have been able to get without your assistance. I’m working on a huge worksheet that will have multiple single select drop down lists (I can do that), several same-cell multiple select drop down lists (thanks to you I can now do that), and several same-cell multiple select add-sort drop down lists. The last is where I’m getting stuck. I want to make selected columns function like the LineBreakAddSort but so they display in the same cell like the SameCell function does. I have figured out how to change from comma seperated to line break and back so that is not the problem.
I’ve been fidgeting with it and have it where on my sammple (not the final worksheet) I can get 3 separate columns to each allow multiple selects from their own lists and to allow write in contents. I could live with this if needed. But what I want and have not been able to get to work is to make the selected columns (not all) so that write in selections are written to the lists and the lists are sorted.
Below is what I have now. This runs but does not add the write ins to the lists. Where did I mess up??
Thanks so much to any and all for any assistance and for making this resource available. Pat
‘ Developed by Contextures Inc.
‘ http://www.contextures.com (change by Pat based on samecell)
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 = 2 Or Target.Column = 3 Or Target.Column = 6 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
Application.EnableEvents = True
‘Dim rngDV As Range (commented out because it threw an error)
‘If Target.Count > 1 Then GoToExitHandler (commented out because it threw an error)
Application.EnableEvents = False
Dim ws As Worksheets
Dim i As Integer
Set ws = Worksheets(“List”)
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
If Target.Value = “” Then GoTo exitHandler
‘add new items to the list
If Application.WorksheetFunction _
.CountIf(ws.Range(“NameList”), Target.Value) Then
‘do nothing
Else
i = ws.Cells(Rows.Count, 3).End(xlUp).Row + 1
ws.Range(“C” & i).Value = Target.Value
ws.Range(“C1?).CurrentRegionName = “NameList”
ws.Range(“NameList”).Sort Key1:=ws.Range(“C1?), _
Order1:=xlAscending, Header:=xlGuess, _
OderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
How can I get the results to come up 2 columns over? I finally got it to work, but I would like to have a column between the list and the results. Thanks!
The code works perfectly as long as I don’t try to protect my worksheet. I removed the code on the “ThisWorksheet” tab but I am still unable to protect the sheet. Any help would be greatly appreciated.
TB, make sure that the data validation cells are unlocked, before you protect the worksheet.
In the code, you can add a line at the top, to unprotect the sheet — include a password, if you used one:
Me.Unprotect Password:=”password”
In the ExitHandler, turn the protection back on:
Me.Protect Password:=”password”
@Riles, if you’re using the SeparateRows example, which puts the selects in the column to the right, you can change this line in the code:
If Target.Offset(0, 1).Value = “” Then
to this
If Target.Offset(0, 2).Value = “” Then