Why does VBA generates a "Method or data member not found" error on a ComboBox, only when exiting Excel?

Question

I have two ComboBoxes, Sheet1.Line_Items, and Sheet1.SerialNumber and some code that interacts with the two. Overall code works fine, but generates an "Method or data member not found" error when exiting Excel.

Is there a way to prevent that error from happening, or even a way to suppress the error so that users are not inconvenienced by the error when closing Excel?

My code:

Private Sub Line_Items_Change() ' Line_Items is an ActiveX ComboBox 

Dim ar As Variant

Dim i As Integer
Dim data As Dictionary

If Sheet1.Line_Items.Value Then
    ar = GetSerialNumber(Sheet1.Line_Items.Value)
Else
    ar = GetNoChoice
End If

With Sheet1.SerialNumber ` an ActiveX ComboBox - Generates ERROR on this line
    .ColumnCount = 2
    .ColumnWidths = "0;60"
End With

When SerialNumber is working (while Excel is open)

The only thing I can figure is that SerialNumber gets destroyed somehow upon Excel closing? ComboBoxes get destroyed and yet the _Change method gets called and can no longer find the combo box?

How is Line_Items ActiveX ComboBox populated

I have this code that clears the box, fills it with values, and then selects the first row

        Sheet1.Line_Items.Clear
          
        ' Update Line Items
        With Sheet1.Line_Items
            .ColumnCount = 2
            .ColumnWidths = "0;60"
        End With
                    
        ' Populate the Combo Box
        For i = 0 To UBound(ar, 2)
            With Sheet1.Line_Items
                .AddItem
                .List(i, 0) = ar(0, i)
                .List(i, 1) = ar(1, i)
            End With
        Next i
        
        ' Select first row
        Sheet1.Line_Items.Value = ar(0, 0)

Other Considerations

I have two comboboxes. Updating one updates the other. For example, I am currently thinking that when I close Excel, somehow SerialNumber combobox gets destroyed, and Line_Items value changes, and the Line_Items_change code gets called, and Excel tries to compile it, until it finds the code that refers to the SerialNumber box, which no longer exists. And throws an error

New Error After using OLEObjects

I changed my code from

Sheet1.SerialNumber

to

Dim SerialNumber As ComboBox
Set SerialNumber = Worksheets("Sheet1").OLEObjects(2).Object

The original error went away, but now I am getting this error:

Run-time error '1004': Method 'Worksheets' of objects '_Global' failed...

New Error after fixing _Global error

I changed the code from

Set SerialNumber = Worksheets("Sheet1").OLEObjects(2).Object

to

Set SerialNumber = Application.ThisWorkbook.Worksheets(1).OLEObjects(2).Object

The error message code remained the same, but the error message changed to:

Unable to get the Object property of the OLEObject class

Next Error - Unable to get the Object property of the OLEObject class

Top portion is during normal operation. Bottom portion is after I close Excel file, it throws Debug error of "Unable to get the Object property of the OLEObject class", Run-time error '1004'

Error happens on this line:

    MsgBox TypeName(Application.ThisWorkbook.Worksheets(1).OLEObjects("SerialNumber").Object)

Sheet1 does not have a SerialNumber member, and the code is failing to compile. Verify that Sheet1 indeed has an ActiveX combobox named SerialNumber. Once the code compiles, the problem will be gone. — Mathieu Guindon

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2021-03-29T15:38:43

SerialNumber does not exist, so the code doesn't compile.

Make sure there is a combobox ActiveX control named SerialNumber on Sheet1.

The compile error would manifest itself much earlier than "when exiting Excel", for example whenever any code in Sheet1 gets invoked.

The macro is not working, if VBA cannot compile the code then no code in the module can run.

Maybe there's code that removes the SerialNumber combobox at run-time, or maybe the control was accidentally deleted or renamed. But no code in a given module can run if VBA can't compile it.

Why does VBA generates a "Method or data member not found" error on a ComboBox, only when exiting Excel?

1 Answers