Embedded Combobox on Excel Worksheet Problem

[drrocket5292]

Ive embedded a combobox on a worksheet using the combobox in the worksheets control toolbox and I can't seem to populate it. I have an Access table with two fields in it that I want to use to populate it. Everytime I try something I get an error saying that the column property of the combobox could not be set - invalid property array index. As far as i can tell this combobox has no rowsource property and seems to missing quite a few properties that the userform combobox has. Anyways, I've tried changing my code 20 different ways and cant get it to fill in the combobox. Here's what I currently have written:

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

If Workbooks.Count = 0 Then
Exit Sub
End If

If Trim(Range("B1").Value) = "NFP Securities, Inc./NFP Insurance Services, Inc." And Trim(Range("B4").Value) = _
"Vendor Name:" And Trim(Range("B5").Value) = "Check/Wire Total:" Then

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stConn As String
Dim stSQL As String
Dim i As Integer
Dim ws As Worksheet
Dim k As Integer
Dim MyArray()
Dim l As Integer

i = 2

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

stConn = "Provider='Microsoft.Jet.OLEDB.4.0';"
stConn = stConn & "Data Source='J:\Michael\REPORTS\2006\Cash Receipts\CashReceipts.mdb';"

cnt.Open stConn

stSQL = "Select * from Carriers order by Carrier"

rst.Open stSQL, cnt

activesheet.ComboBox1.ColumnCount = 2


l = 1
rst.MoveFirst
Do While Not rst.EOF
l = l + 1
rst.MoveNext
Loop

rst.MoveFirst

ActiveSheet.ComboBox1.Clear

For k = 0 To l - 1
ActiveSheet.ComboBox1.Column(k, 0) = rst.Fields("CarrierID")
ActiveSheet.ComboBox1.Column(k, 1) = rst.Fields("Carrier")
rst.MoveNext
Next k

Else

Exit Sub
End If

End Sub


I've tried throwing my two recordset fields into an array and passing it that way and ive tried leaving out the columns column and row properties as well and it still wont get past my invalid use of the columns property. does anyone know what i'm doing wrong? thanks for the help.

 

[firefytr]

This should get you what you need ...

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stConn As String
    Dim stSQL As String
    Dim i As Integer
    Dim ws As Worksheet
    Dim k As Integer
    Dim MyArray(), arrItems()
    Dim l As Integer

    If Workbooks.Count = 0 Then
        Exit Sub
    End If

    If Trim(Range("B1").Value) = "NFP Securities, Inc./NFP Insurance Services, Inc." And Trim(Range("B4").Value) = _
       "Vendor Name:" And Trim(Range("B5").Value) = "Check/Wire Total:" Then

        i = 2

        Set cnt = New ADODB.Connection
        Set rst = New ADODB.Recordset

        stConn = "Provider='Microsoft.Jet.OLEDB.4.0';"
        stConn = stConn & "Data Source='J:\Michael\REPORTS\2006\Cash Receipts\CashReceipts.mdb';"

        cnt.Open stConn

        stSQL = "Select * from Carriers order by Carrier"

        rst.Open stSQL, cnt

        Me.ComboBox1.ColumnCount = 2

        '####### Temporary testing array
        arrItems(0, 1) = "1"
        arrItems(0, 2) = "One"
        arrItems(1, 1) = "2"
        arrItems(1, 2) = "Two"
        arrItems(2, 1) = "3"
        arrItems(2, 2) = "Three"
        
        '####### Set combobox from array
        Me.ComboBox1.List = arrItems

    Else

        Exit Sub
    End If

End Sub

I did substitute my test array for one you did not have (I didn't see it anyway). Just populate your array however you want to and just set it in one fell swoop with the .List method.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[drrocket5292]

thanks for the help firefytr, that did the trick. I appreciate it.