I am searching for a better way to disable ActiveX events from triggering in an Excel workbook (though this would apply to all Office apps with ActiveX objects).
Hopefully something similar to Application.EnableEvents = false
, though this does not work with ActiveX.
In the below example it's trivial to use a global boolean but I have a lot of event handlers for my ActiveX objects and it would be immensely easier for something I could universally apply to temporarily disable ActiveX events. I don't really want to add an if/exit sub statement to every single one of these methods.
To demonstrate this problem, create an ActiveX combobox in a worksheet and add the following to that sheet module
Public initializingContent As Boolean
Private Sub intializeAllActiveXContent()
'this doesn't apply to activeX events :'(
Application.EnableEvents = False
'this could work but is not really elegant
'change this to false to show my problem in
'the intermediate window (called not once but twice)
initializingContent = True
ComboBoxTest.Clear
ComboBoxTest.AddItem ("item1")
ComboBoxTest.AddItem ("item2")
ComboBoxTest.AddItem ("item3")
'select the top value in the box
ComboBoxTest.value = "item1"
initializingContent = False
Application.EnableEvents = True
End Sub
Private Sub ComboBoxTest_Change()
'I really don't want to have to wrap EVERY single ActiveX method
'with something like this for a whole variety of reasons...
If initializingContent Then Exit Sub
Debug.Print "do stuff I don't want to happen when intializeAllActiveXContent() runs " & _
"but I do when user changes box"
End Sub