Excel VBA Object required error when populating ComboBox

Question

I am getting the following error --

Run-time error '424' : Object required

Here is the code where I am getting the error message. The line where the error appears has been highlighted with ****

    Sub LoadDropdown_Click()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String, stSQL As String
Dim xlCalc As XlCalculation
Dim vaData As Variant
Dim k As Long

Set cnt = New ADODB.Connection

cnt.connectionString = Module1.GetConnectionString
stSQL = "EXEC dbo.GetData"


With cnt
    .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
    .Open stConn 'Open connection.
     'Instantiate the Recordsetobject and execute the SQL-state.
    Set rst = .Execute(stSQL)
End With

With rst
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
    k = .Fields.Count
     'Populate the array with the whole recordset.
    vaData = .GetRows
End With

 'Close the connection.
cnt.Close

 'Manipulate the Listbox's properties and show the form.
With ComboBox21
        .Clear       ' **** the error comes at this line since ComboBox21 is empty ******
        .BoundColumn = k
        .List = Application.Transpose(vaData)
        .ListIndex = -1

End With

Dim i As Integer

 'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing


    End Sub

These are the things I have veified --

The ComboBox actually exists in Sheet1, called Priorities. See this screenshot showing that Sheet1 contains a Combobox called ComboBox21
The below function, LoadDropdown_Click is present in Sheet1. See this screenshot for details
This code works when running from certain machines. It used to work on my machine earlier, but now I am getting this error suddenly without having made any changes to the code or the environment.
I tried changing ComboBox21 to Sheet1.ComboBox21, but I got a compile error - Method or Data member not found.

It will be great if someone can help!

"Object Required" in this situation indicates two things: 1) you're not using Option Explicit, and 2) ComboBox21 apparently isn't in-scope, try qualifying it like @RobinMackenzie suggested. Also, specify Option Explicit at the top of every module, and declare all your variables. — Mathieu Guindon
Also I'd strongly recommend you rename your control to PrioritySelection or something similarly meaningful. ComboBox21 means nothing. — Mathieu Guindon
When I change it to Sheet1.ComboBox21, then I get a compile error - Method or Data member not found — Sameer

Nikolaos Polygenis Nikolaos Polygenis · Accepted Answer · 2017-09-09T10:48:08

Please change your code:

 With ComboBox21
            .Clear       ' **** the error comes at this line since ComboBox21 is empty ******
            .BoundColumn = k
            .List = Application.Transpose(vaData)
            .ListIndex = -1

    End With

'With the below:

     Sheet1.ComboBox21.Clear

With Sheet1.ComboBox21
        .BoundColumn = k
        .List = Application.Transpose(vaData)
        .ListIndex = -1
End With

Excel VBA Object required error when populating ComboBox

2 Answers