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,
This is excellent – thank you so much for this tutorial – it is extremely helpful.
I have one question, I have made my own lists and named them etc but I can’t find where to change the list in the VBA code.
I look forward to your response.
Hi,
I’ve copied the VB Code. It worked but then, it stopped working. I don’t understand why.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:=”Form_”
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 = 12 Then
If oldVal = “” Then
Else
If newVal = “” Then
Else
Target.Value = oldVal & “, ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
ActiveSheet.Protect Password:=”Form_”
End Sub
————-
I don’t understand why it is not working. And by the same time, I find the cell becomes crowded when the person will be selecting more than 2 so my question is: Is there a way to make the cell expend by itself when the content is to big?
Thank you
Nancy
Debra, forget my last message. I’ve seen that you had an example of the one with the line break. I juste downloaded it. Copied it into my worksheet VBA code but… nothing happenned. Is there something I should be changing?
Thank you
Nancy
@Nancy, make sure you have macros enabled in the workbook.
Hi Debra,
This blog has been very helpful to me so thanks.
Secondly, I have gone a little beyond just using the data validation list box and instead used a multiselect activeX listbox with checkboxes to select the items that I want stored, comma delimited, in the active cell. This allows me to select and deselect the items I want stored in the cell all at once. It all works fine until I go to edit a cell that has already got data stored in it, as when I open the listbox all of the items are deselected (if I were to click on the OK button straight after opening the listbox, to store the listbox value, it would delete all of the data already stored in the cell). This could lead to a lot of errors. What I would prefer is to populate the listbox checkboxes with checks according to the values that were stored in the cell upon opening the list box.
Is this possible?
If so do you have any tips on how to go about it?
Thanks
Flip
Debra,
Glad to see that you’re still answering questions. I’ve been using the code for a while, and it works very well – until I start inserting rows and rewriting named ranges. Then it reverts back to one selection only. I get the feeling that I’m missing resetting a range value somewhere in the worksheet. What can I test to make sure that the multiple event actions will work?
My code basically reads in a csv file, reformats it, checks for duplicates, adds new rows into the existing worksheet, copies the reformatted data to them, and then creates directories based on document numbers. However, after the insert process is done, the multiple list pulldown no longer works (but it does work as a single pulldown). Is there something obvious going on? Thanks.
Looking at it a bit closer, I’m never getting past the second Target count block of code:
If Target.Count > 1 Then GoTo exitHandler
‘ I put a msgbox – the Target.Count is equal to 1
On Error Resume Next
‘I have a message box here – and reach before the Set
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
‘ I have a message box here as well – and never reach this section of code – I’m breaking at the rngDV command.
Any suggestions?