List Box AddItems problems

[Westicle]

Hi Y'all

I am having trouble adding items into a list box (from a spreadsheet). I can add a single item fine but I am stumped as to how i can have more columns. The List box is detailing a company car history of drivers - column 1 is name, 2 is start date, 3 is end date. This is what I have got so far:

For Each Cell In CarDriverHistorySource.Range("B:B&quot
If Cell.Text = Reg Then
HIr = Cell.Row
DriverHistoryLB.AddItem (CarDriverHistorySource.Cells(HIr, 4).Text)
End If
Next

column 4 is the driver name, 5 is start date and 6 is end date.

Answers on a postcard....

 

[RobBroekhuis]

Easiest way is to assign all the items to an array, then assign the array to the .list property of the listbox.
Rob

 

[SteveBausch]

.RowSource should work, have you tried it?

Best to set it at run-time, you can ensure the correct sheet is being referenced.

  ListBox1.RowSource=sheet1.Range(cells(1,1).address,cells(4,4).address).Address

It is tricky to set .RowSource at design-time, I don't do it that way, since I have only been able to use the sheet tab name to reference the sheet. If you were to enter the above code in the Immediate pane, the Properties pane would indicate:

sheetOne!A1:A5

"SheetOne" is the .Name of the worksheet (shown on tab). "Sheet1" is the .CodeName of the worksheet (Shown in parentheses, of Project Explorer)