Multiple Selections from a Drop-Down Menu

[Guest]

Does anyone know if it is possible to make multiple selection from a drop down menu

Ex. I have a drop-down list that contains all the countries in the world. I would like to be able to have a user select any combination of countries from the drop-down list in a single cell (and have that selection appear in the cell). Anyone know if this is possible? Ideally, I'd like to have a line return after each country in the cell for formating purposes.

 

[Debra Dalgleish]

You can only select one item from a data validation list. However, you
could use an event procedure to add items from the list to an adjacent
cell. For example, with the data validation list in cell C3:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$3" Then
If Target.Value = "" Then Exit Sub
If Range("C4").Value = "" Then
Range("C4").Value = Target.Value
Else
Range("C4").Value = Range("C4").Value _
& Chr(10) & Target.Value
End If
End If
End Sub

 

[Guest]

Thanks Debra .. that's awesome! One add-on question, my drop down menus run for multiple rows, is there any way to apply the macro to a range and have it copy when someone adds new rows

ex. I have drop down menus the run from C3 to C25. I have a macro that enables users to add rows to this range (ie. C# to C25 stretches to C3 to C29 say). I'd like to have the macro you sent apply to every cell in the range (and the new cells users may add). Is that possible

Thanks again, Sean

 

[Debra Dalgleish]

You're welcome! To adapt the macro to any cell with data validation in
column C, you could change to something like the following. It stores
the selected values in the cell to the right:

'=============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Column = 3 Then
If Target.Value = "" Then Exit Sub
If Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Value = Target.Value
Else
Target.Offset(0, 1).Value = _
Target.Offset(0, 1).Value _
& Chr(10) & Target.Value
End If
End If
End If
Application.EnableEvents = True
End Sub
'=========================