Select Multiple Items and return value for Dropdown or listbox
 

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?

Select Multiple Items and return value for Dropdown or listbox
 

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


On 19 jan, 19:22, kookie wrote:
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?

Select Multiple Items and return value for Dropdown or listbox
 

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

On Mon, 19 Jan 2009 10:22:00 -0800, kookie
wrote:

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?

Select Multiple Items and return value for Dropdown or listbox
 

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?

"Gord Dibben" wrote:

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

On Mon, 19 Jan 2009 10:22:00 -0800, kookie
wrote:

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?

Select Multiple Items and return value for Dropdown or listbox
 

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

On Mon, 19 Jan 2009 13:14:00 -0800, kookie
wrote:

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?

"Gord Dibben" wrote:

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

On Mon, 19 Jan 2009 10:22:00 -0800, kookie
wrote:

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?