Object doesn't support this property or method

[JeffK627]

I'm getting the above error when I try to add items from a recordset to a combo box. One thing I noticed is that when I typed in "DropDown2" the autocomplete didn't capitalize it - in fact, when I typed "ThisWorkbook.Worksheets("Sheet5")." it didn't offer me any choices after the dot. The same thing happened when I tried typing 5 (without quotes) instead of "Sheet5". The reference to MS Forms 2.0 object library is added. This is in Excel 2007. Here's the code, any insight is appreciated!

Private Sub Workbook_Open()

    Dim cnt As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim stSQL1 As String
    
    Set cnt = openConn
    Set rst1 = New ADODB.Recordset
    stSQL1 = "SELECT fname, lname FROM TestTable1"
    
    With rst1
        .Open stSQL1, cnt 'Create the recordset.
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
    End With

    While Not rst1.EOF
        With ThisWorkbook.Worksheets("Sheet5").DropDown2
          .AddItem (rst1.Fields("fname") & " " & rst1.Fields("lname"))
          rst1.MoveNext
        End With
    Wend
    
    rst1.Close
    Set rst1 = Nothing
    cnt.Close
    Set cnt = Nothing

End Sub

 

[Gary McMaster]

For ActiveX it would be something like this:

With ThisWorkbook.Worksheets("Sheet5").OLEObjects("DropDown2")

For Form controls it would be something like this:

With ThisWorkbook.Worksheets("Sheet5").Shapes("DropDown2")

Gary

 

[JeffK627]

Thanks, Gary - actually, when I deleted it and re-inserted it as an ActiveX combo box I didn't even need the "OLEObjects" qualifier, it came up as a member of the worksheet.