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 have created the file with datavalidation drop down list but while e-mailing the same the same is disappearing (code) from the file. The receipient also want to see the changes? Can someone help on this
@Terry, you could use Select Case and list the cell addresses, e.g.:
Select Case Target.Address Case “$A$7”, “$C$5” ‘code here End SelectWhere in the code do I paste this
Select Case Target.Address
Case “$A$7”, “$C$5”
‘code here
End Select
In order to add multiple columns for this coding function please? I have several and cannot seem to separate them by a comma.
Many thanks!!
@Dora, the Select Case…End Select would replace the If Target.Column…End If lines in the code.
HI Debra
Could you clarify the select case address- as copy paste this part into the macro did not work; could you point out with the full eg please
[…] where you need several cells with multiple options, you could use the technique for selecting multiple items from a data validation drop […]
Hi Debra,
This examples are awesome! Can you help me, Im trying to add the ‘SameCell’ code to this worksheet that already has code. I changed the target column to 14, but it seems to be executing the code on the other cells that have data validation as well. When it executes the code, it returns an error. The error is: “Compile error: Ambiguous name detected: Worksheet_Change”
and then it highlights the following code: Private Sub Worksheet_Change(ByVal Target As Range) of the code I copied.
Im wondering is it having problems with the other code thats on the sheet? If so, what do I need to change so it doesn’t execute on all the data validation cells, only on column 14?
This is the current code on the sheet I am using… Thanks a Million!
Private Sub Worksheet_Change(ByVal Target As Range)
Const NumQuarters As Long = 10000
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(0, -2).Value = Now
Target.Offset(0, -3).Value = NumQuarters + (Target.Row – 3)
Application.EnableEvents = True
End If
Application.EnableEvents = True
End Sub
‘ Job 2 —————————— WORKING ———————————————-
‘
‘—————————– Data Validation add in same cell ———————–
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 = 14 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 Alex. Once again, hope Debra doesn’t mind me answering but I saw this tonight and thought I’d help you out on the quick.
You need to take out your second reference:
Private Sub Worksheet_Change(ByVal Target As Range)
Just make it all one piece of code. You can’t have two references to the same Worksheet_Change Subcode. Make it like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Const NumQuarters As Long = 10000
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(0, -2).Value = Now
Target.Offset(0, -3).Value = NumQuarters + (Target.Row – 3)
Application.EnableEvents = True
End If
Application.EnableEvents = True
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 = 14 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
See what that does for you.
LB
Wow! Thank you so much LB! It works great and thank you Debra for the ease you bring to us users to become better at Excel!