List boxes

[Joe Powers]

Hi,
I have an option button on a worksheet that activates a
list box when the user clicks it. I ask the user to select
up to 15 items in the list box. I would like to take the
user's selections from the list box and store them in a
column on another worksheet in the workbook. Does anyone
have any idea/code that I could use to accomplish this
task?

 

[Simon Livings]

There are a few methods for doing this, but the following code is
probably the simplest (not tested):

Sub PrintSelection()
Dim i As Integer, iCount As Integer

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
iCount = iCount + 1
Cells(iCount, 1) = ListBox1.List(i)
End If
Next i

End Sub


HTH
Simon Livings

 

[Joe Powers]

Simon,
I have added the code you sent me to the Private Sub
ListBox_Hosp_Click() portion of my list box, but when I make my
selections, the selections do not appear anywhere on my worksheet?
Here is what I have:
'Private Sub ListBox_Hosp_Click()
Sub PrintSelection()
Dim i As Integer, iCount As Integer

For i = 0 To ListBox_Hosp.ListCount - 1
If ListBox_Hosp.Selected(i) Then
iCount = iCount + 1
Cells(iCount, 1) = ListBox_Hosp.list(i)
End If
Next i

End Sub
'End Sub

Any thoughts?
Joe

 

[Norman Jones]

Hi Joe,

I tried Simon's code in the ListBox DblClick event , and it functioned, as
intended, entering the selected values in cells A1 downwards.

 

[Joe Powers]

Norman,
You are correct, I changed the event on my list box to DblClick and the
code Simon sent does work. One more question. I only want the user to
select up to 15 data points from my list box, is there a way to modify
Simon's code to accomplish this?
TIA
Joe