Excel listbox question 1

[vedicman]

I'm using the following SQL statement to create a recordset for populating a listbox:

SQL = "SELECT TEMRepCode, TEMRepName " & _
"FROM TEMREP " & _
"WHERE Status IN ('I','P') " & _
"Order By TEMRepName"

I'm attempting to populate the listbox with this code:

Do Until rs.EOF
Sheets("Entry").lstSalesReps.AddItem rs!TEMRepCode
Sheets("Entry").lstSalesReps.AddItem rs!TEMRepName
rs.MoveNext
Loop

Instead of going into 2 columns, only 1 column is being created alternating between TEMRepCode & TEMRepName

I have the list box propeties set as follows:
BoundColumn: 1
ColumnCount: 2

Please provide a code smaple of what I need to do differently.

Thanks.....Franco

 

[PHV]

Typed, untested:
Sheets("Entry").lstSalesReps.List() = rs.GetRows()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vedicman]

It only gives one TEMSalesRepCode & one TEMSalesRep, one on top of the other.

Any other ideas?

 

[PHV]

And what about this ?
With Sheets("Entry").lstSalesReps
Do Until rs.EOF
.AddItem rs!TEMRepCode
.List(ListCount - 1, 1) = rs!TEMRepName
rs.MoveNext
Loop
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GVF]

I believe you must fill multiple columns in a listbox using an array and the .Column method.

Dim RepArray() As String
ReDim RepArray(1, 1)
Counter = 0
Do Until rs.EOF
RepArray(0, Counter) = rs!TEMRepCode
RepArray(1, Counter) = rs!TEMRepName
Counter = Counter + 1
ReDim Preserve RepArray(1, Counter)
rs.MoveNext
Loop
ReDim Preserve RepArray(1, Counter - 1)
Sheets("Entry").lstSalesReps.Column = RepArray()

I think that'll work.
By the way, if you ever get Listbox ColumHeaders to work you will win a prize.

Greg

 

[vedicman]

Greg

That did the trick. Thanks for the code!

Franco