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 !
Your code is exactly what I was looking for !, so firstly thank you for that. I was wondering if you could tell me how I can change the code below so that I can change the location of where the data validation cell is and where the data in separate rows is entered. For example, I would like the data validation cell to be somewhere in Column G and the data appearing in rows somewhere in Column B.
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
1 Col = Target.Column ‘column with data validation cell
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
If Target.Value = “” Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 7, 12, 14, 18
If Target.Offset(0, 1).Value = “” Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 1).Value = Target.Value
Target.ClearContents
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
Thanks !
Hi Debra – first of all let me thank you for all the wonderful scripts etc. I have found them very very useful. Over the weekend I was using your multiple drop down menu script which was exactly what I needed and it worked beautifully!
However, like one of the posters in this blogg (Angela), I found the date column was reversing the date format when the script was used – removing the script put the date column right again. Instead of dd/mm/yyyy, it would convert it to mm/dd/yyyy. I tried Angela’s workaround but it did not work for me.
The solution that worked perfectly was very simple. I inserted a new column next to the offending date column, copied and pasted the info into the new column then deleted the origonal one.
Everything works fine now – brilliant!! Presumably, whilst applying and configuring the worksheet something became corrupted.
I thought I would mention this as there may be others who have the same problem as Angela and myself have experienced and this quick fix might do the trick for them also.
Again, thank you for all your clever scripts and advice on this site.
Is there anyway to just allow the code to effect an individual cell instead of a the whole column. I’m building a data entry form and have many different validations within a single column.
Thanks,
[…] a popular sample file on my website, that lets you select multiple items from a data validation drop down list. Since the original article, I’ve posted […]
This is ideal for what I have been trying to do. Thanks very much.
One slight query would be that if I then filter the columns is there any way of just having the original drop down selections appear rather than the new, multiple selections I create using this solution.
As an example, if I was classifying fruit and had the drop down options green & round I would use this solution to give an apple the classification ‘Green, Round’ whereas a pear would just be ‘Green’.
When I go to filter this using the normal column drop down filters, the options I get are ‘Green’ , ‘Round’ & ‘Green, Round’. It is this last one I wish to prevent from seeing.
i realise this is above and beyond what you have tried to achive in this bit of code but just thought Id ask 🙂
Thanks
I came across your code and it works wonderfully. Thank you. I am using the code which allows multiple selections from a dropdown list separated by a comma in my worksheet. This works fine as my worksheet is unprotected. I need to have this functionality (protection) because I have formulas that I want to hide and cells that I need to lock. Anyway to get these requirements working together? Thanks in advance!