How to populate a multi-column activeX listbox on a spreadsheet with an ADO recordset

[Guest]

Hello, I'm using VBA in Excel XP with Win 2000

Does anyone know how to populate a multi-column activeX listbox on a spreadsheet with an ADO recordset

I am returning the field count and setting the number of columns needed in the list box, but I can't figure out how to load the list box with the data

Can I use "CopyFromRecordSet" some how? Must I convert the RecordSet into an array? What

Please supply example code. Thanks much in advance.

 

[Bob Phillips]

If you use CopyFromRecordset to poulate a worksheet range, you could then
poiunt the Listbox at that

Worksheets("Sheet2").Range("A1").CopyFromRecordset rsData
With Me.ListBox1
.ColumnCount = 3
.RowSource = "Sheet2!A1:C4"
End With

or you could load the recordset to an array and use that directly

ary = RS.GetRows()
With Me.ListBox1
.ColumnCount = 3
.List = ary
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello, I'm using VBA in Excel XP with Win 2000.

Does anyone know how to populate a multi-column activeX listbox on a

spreadsheet with an ADO recordset?

I am returning the field count and setting the number of columns needed in

the list box, but I can't figure out how to load the list box with the data.