Select Multiple Values in Dropdown Box (Validation List)

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

This is a "repost"... I didn't get any replies a few days ago... maybe I'm
luckier this time.


I use a validation list that brings up combo boxes in a cell range.

Does anyone know if I could select multiple values of the combo box?
Something like holding down CTRL and then clicking on multiple values in the
drop-down menu (as it can be found on websites). All of the chosen values
should then be stored in the selected cell (maybe separated by commas).
 
If you're referring to a data validation dropdown list, you can only
select one item. However, you could use an event procedure to add items
from the list to an adjacent cell.

For example, if cells in column C have data validation, the following
code 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
'=========================

Store this code on the module of the sheet that contains the data
validation cells. Right-click the sheet tab, and choose View Code. Paste
the code onto the module sheet, where the cursor is flashing.
 
Debra:

Wow, that works great! One question though... currently each "new" item is
entered on a different line in the same cell. This makes the height of the
cell grow each time...

Is there a way to display all values on the same line with a "comma"
separator?
 
Change the Chr(10) to ", "

'============
Else
Target.Offset(0, 1).Value = _
Target.Offset(0, 1).Value _
& ", " & Target.Value
'================
 
PERFECT!!!

Thanks, Debra.

--
Tom


Debra Dalgleish said:
Change the Chr(10) to ", "

'============
Else
Target.Offset(0, 1).Value = _
Target.Offset(0, 1).Value _
& ", " & Target.Value
'================
Click to expand...
 
Back
Top