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:
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...
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