Hi Paula
Only with code
This code (not written by me) will allow you to make multiple selections
from a data validation cell, and will insert a comma between each
selected value
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String, newVal As String
Dim tr As Long
tr = Target.Row
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 oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
It is event code so you need to copy it to the sheet where you have your
cells with Data Validation.
Copy code above
Right click on sheet tab>View Code
Paste code into white pane that appears
Alt+F11 to return to Excel.
