vbaexcelactivex

ActiveX ComboBox_Change event falsely triggering a Compile Error: Variable not defined


This is being triggered by my Option Explicit at the top of my module. When I open my workbook, my cbSelectData_Change event fires (for whatever reason), which then says my variable cbStartDate is not defined (i'll include the lines but it shouldn't be relevant:

topnum = ThisWorkbook.Worksheets("Data Pull").Columns(3).Find(What:=cbStartDate.Value, LookIn:=xlValues).Row
botnum = ThisWorkbook.Worksheets("Data Pull").Columns(3).Find(What:=cbEndDate.Value, LookIn:=xlValues).Row

cbSelectData is an ActiveX ComboBox containing a data category that charts populate off of. cbStartDate and cbEndDate are ActiveX ComboBoxes that alter the date range displayed on the chart x-axies.

I can confirm that I have a cbStartDate and a cbEndDate on my worksheet:

img1

In fact, when I just exit the debugger (twice) my file will work fine; I can freely change my cbStartDate and cbEndDate and my charts update as expected, and I never see the compile error again (until re-opening the file).

My Suspicion:

This has something to do with the instantiations of the ActiveX controls - while cbSelectData is loaded (and firing its Change event), it's not finding cbStartDate or cbEndDate because they're not loaded yet.

What I need to do:

I either need to stop my cbSelectData_Change event from firing when I open the file, or I need to force the loading of my cbStartDate and cbEndDate before this event fires. I've messed around with Application.EnableEvents to no avail - any insight to this would be appreciated.

Note: These have to be ActiveX controls for formatting purposes. I also know that I can remove my Option Explicit altogether, but I'd REALLY rather not...


Solution

  • I'm not sure what broke it, but ActiveX controls can be flaky.

    Try retrieving the object at run-time instead of compile-time:

    Public Function FindActiveXControl(ByVal sh As Worksheet, ByVal name As String) As Object
    
        Dim objects As OLEObjects
        Set objects = sh.OLEObjects
    
        On Error Resume Next
            Dim oleControl As OLEObject
            Set oleControl = objects.Item(name)
        On Error GoTo 0
    
        If Not oleControl Is Nothing Then Set FindActiveXControl = oleControl.Object
    
    End Function
    

    Now you can work with locals, and handle the weird case where the control wouldn't be found:

    Dim sheet As Worksheet
    Set sheet = Me
    
    Dim startDateControl As MSForms.ComboBox
    Set startDateControl = FindActiveXControl(sheet, "cbStartDate")
    If Not startDateControl Is Nothing Then
        topnum = ThisWorkbook.Worksheets("Data Pull").Columns(3).Find(What:=startDateControl.Value, LookIn:=xlValues).Row
    End If
    
    Dim endDateControl As MSForms.ComboBox
    Set endDateControl = FindActiveXControl(sheet, "cbEndDate")
    If Not endDateControl Is Nothing Then
        botnum = ThisWorkbook.Worksheets("Data Pull").Columns(3).Find(What:=endDateControl.Value, LookIn:=xlValues).Row
    End If