Populating a listbox in a user form with multiple cells for the items listed..

[KimberlyC]

Hi.

I am using the following code to populate a listbox in a user form with data
on a worksheet.

Dim x As Long

For x = 4 To 13

listbox1.AddItem (ActiveSheet.Cells(9, x))

Next


End Sub

This is working great.. however.. I now would like to include two more cells
going down from cells 9 to 11 to display in the listbox for each item.
For example.... if cell D9 has "Kim" entered and D10 has "Smith" and D11
has "Manager "... I would like the list box to show " Kim Smith Manger " for
the item listed.
I'm not sure how to do this... or if it's possible.

Any help is greatly appreciated...
Thanks!
Kimberly

 

[Michael J. Malinsky]

Something like this might work:

Sub UserForm_Initialize()

Dim x As Integer

ListBox1.ColumnCount = 3

For x = 4 To 13
ListBox1.AddItem (ActiveSheet.Cells(9, x).Value)
ListBox1.List(ListBox1.ListCount - 1, 1) = _
ActiveSheet.Cells(10, x).Value
ListBox1.List(ListBox1.ListCount - 1, 2) = _
ActiveSheet.Cells(11, x).Value
Next x


End Sub


--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[KimberlyC]

Thanks!!

That worked..
Now I have another question.....
When the listbox is populated with those cells..the user then clicks on the
items that they want copied to another worksheet. Here is the code I'm
running to do that.....but I really need it to copy all three cells going
down (like d9, d10, and d11) for the item selected....and it's only coping
the first cell (d9, e9 and so on...)
How do I change this code to allow the three cells to be copied for the item
selected?

Dim i As Integer
Dim j As Integer

With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Range("Descriptions").Offset(j, 0).Value = .List(i)
j = j + 1
End If
Next i
End With