Select Multiple Items and return value for Dropdown or listbox

K

kookie

I have a field I am trying to return the values. I can add a data validation
on a list of languages, but this only returns 1 option.
Is there a way to capture this and add to it each time an additional item is
selected?

or with a listbox that already allows for multiple select, How do you return
the value(s) to another cell comma delimited?
 
J

JB

Operator choice more than one item
(colonne B is hidden)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" And Target.Count = 1 Then
Application.EnableEvents = False
p = InStr(Target.Offset(0, -1), Target.Value)
If p > 0 Then
Target.Offset(0, -1) = Left(Target.Offset(0, -1), p - 1) & _
Mid(Target.Offset(0, -1), p + Len(Target.Value) + 1)
Else
Target.Offset(0, -1) = Target.Offset(0, -1) & Target.Value & " "
End If
Target.Value = Target.Offset(0, -1)
Application.EnableEvents = True
End If
End Sub

http://cjoint.com/?bvt1f8MdZ1

JB
http://boisgontierjacques.free.fr
 
G

Gord Dibben

Using Data Validation dropdown add this Debra Dalglesih event code to your
worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
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
If Target.Column = 3 Then 'adjust the 3 to your column.
If Target.Value = "" Then GoTo exitHandler
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 _
& ", " & Target.Value
End If
End If
End If

exitHandler:
Application.EnableEvents = True

End Sub


Gord Dibben MS Excel MVP
 
K

kookie

I found this code through another post and was able to make it work, Now I am
trying to count. I want a number value for the number of items selected.

Can this be done?
 
G

Gord Dibben

Since you are building a comma delimited list in a cell you could count
commas and add 1

Function count_commas(r As Range) As Integer
V = r.Formula
count_plus = Len(V) - Len(Replace(V, ",", ""))
End Function


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top