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,
Thank you for the code – very handy.
I made a small adjustment that keeps the macro from adding in the selection if it has already previously been selected, I hope you might find it useful:
Application.EnableEvents = False
commaChk = “,”
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal “” Then
If newVal “” Then
If InStr(1, oldVal, newVal) = 0 Then
Target.Value = oldVal & “, ” & newVal
End If
If InStr(1, oldVal, newVal) > 0 Then
Target.Value = oldVal
End If
If Len(oldVal) + 1 > Len(Target.Value) Then Target.Value = oldVal
End If
End If
I worked around your code, which worked nicely, to enable modification (add, remove) of items in the list.
I removed some lines. Works as long as items do not contains “, “.
Private Sub Worksheet_Activate()
Application.EnableEvents = True
End Sub
' Developed by Contextures Inc.
' www.contextures.com
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 Not Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False
If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If newVal "" Then 'If new value isn't empty
If InStr(1, oldVal, newVal) > 0 Then 'If string is found in current value
If InStr(1, oldVal, newVal) = 1 Then 'If string is found at the beginning
If InStrRev(newVal, ", ") = 0 Then
Target.Value = oldVal 'Means user tried to add string that was already in current string
Else
Target.Value = newVal 'Means user deleted part of the string
End If
Else
Target.Value = oldVal 'Means user tried to add string that was already in current string
End If
Else
If oldVal = "" Then
Target.Value = newVal 'Means field was empty before update
Else
Target.Value = oldVal & ", " & newVal 'We append new value to the old string
End If
End If
Else
Target.Value = "" 'Means user cleared the field or didn't add item
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
I like the general abilities in this code but was wondering if the code to place the selected values in the next open cell could be changed so that I can remove a previously selected item from wherever it was placed. Similar to the idea of removing from the comma delimited string.
Thanks for your help.
Hello,
Not sure if this has been already asked, but I am trying to use a drop down list in Excel 2007 and I do not want it to remove items once they have been selected. For instance, for a transaction log, I want to have the user type in a description of a purchase, and in the column next to it choose from a list of categories this purchase falls into (such as food, or clothes). But then on the next transaction line, if the category item has already been selected once, I cannot select it again in the creel just below (say if there were multiple food or clothing purchases). Is there a way to allow the user to use the list item repeatedly from the drop down boxes in the same column?
never mind, excel 2010 fixed it
Debra,
I just wanted to take the time to thank you. This information was very useful.
Even reading the posts and replies from everyone was a great help as well.
After finishing a project for building a time management sheet for business travelers, I can hardly believe how well it works.
Trent
@Trent, you’re welcome! Thanks for letting me know that it helped with your project.
Hi,
Can u tell me; i want to show validation list matching word by typing some letter, then i select right one.
because if drop list so much then time is wast to scroll down…
Thank You