Using a listbox.

[Paul]

Hi I have a list box that appears in a worksheet when cell
A1 is made active.

The list box is populated with 10 text-phrases from
another worksheet and formated thus:

With ListBox1
.RowSource = "Info!A1:A12"
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
End With

When the user selects an item in the list, I need the
listindex value added to the active cell and removed again
when the user deselects. I need the active cell to hold
multi-selcts in a comma seperated format.

Hope this makes sense. any ideas would be appreciated.

Thanks, Paul
Using XL 2000.

 

[Tom Ogilvy]

Private Sub Listbox1_Click()
Dim sStr as String
With Listbox1
sStr = ""
for i = 0 to .Listcount - 1
if .selected(i) then
sStr = sStr & i & ", "
end if
next
End With
sStr = Left(sStr,len(sStr)-2)
Range("A1").Value = sStr
End Sub

Untested, but should work.

 

[jamieuk]

Untested, but should work.

Sadly no, because the Click event doesn't fire when .MultiSelect =
fmMultiSelectMulti. Also 'i' is undeclared. Here's the (slightly)
amended code:

Private Sub ListBox1_Change()
Dim i As Long
Dim sStr As String
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
sStr = sStr & i & ", "
End If
Next
End With
sStr = Left(sStr, Len(sStr) - 2)
Range("A1").Value = sStr
End Sub

--

 

[Tom Ogilvy]

Good catch - thanks.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message

Sadly no, because the Click event doesn't fire when .MultiSelect =
fmMultiSelectMulti. Also 'i' is undeclared. Here's the (slightly)
amended code:

Private Sub ListBox1_Change()
Dim i As Long
Dim sStr As String
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
sStr = sStr & i & ", "
End If
Next
End With
sStr = Left(sStr, Len(sStr) - 2)
Range("A1").Value = sStr
End Sub