How to populate a form control listbox (not ActiveX) with multiple columns?

[tcarnahan]

Hi,

Due to compatibility issues, I am having to use a form control listbox (vs. ActiveX) control on my spreadsheet. I am trying to populate it with three fields from an ADO recordset, but I am having problems as it has been sometime since I have used Form Controls.

Here is the code I wrote for my ActiveX listbox. Can you point me to an example of where a form control listbox with multiple columns is populated from an ADO recordset?

Thanks ahead of time for any help you can provide in making this conversion!

-- Tom

Private Sub Worksheet_Activate()
On Error GoTo Err_Worksheet_Activate
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim i As Integer

    Set cnn = New ADODB.Connection

    cnn.ConnectionString = gcstr_Connection ' a global property with the connection string defined elsewhere. The connection is to a SQL Server database.
    
     cnn.Open
     
     Set rst = cnn.Execute("SELECT [FieldA], [FieldB], [FieldC] FROM dbo.<mytable>", , adCmdText)
     
     rst.MoveFirst
     
     With Me.lst_System
         .Clear
         Do
         .AddItem
         .List(i, 0) = rst![FieldA]
         .List(i, 1) = rst![FieldB]
         .List(i, 2) = rst![FieldC]
         i = i + 1
             rst.MoveNext
         Loop Until rst.EOF
     End With
     
Exit_Worksheet_Activate:
     On Error Resume Next
     rst.Close
     cnn.Close
     Set rst = Nothing
     Set cnn = Nothing
     Exit Sub
     
Err_Worksheet_Activate:
     MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
     Err.Clear
     Resume Exit_Worksheet_Activate
End Sub

</mytable>