Background/Introduction
In an effort to tame wild ActiveX objects, I am implementing custom event handlers which I can assign to each ActiveX control.
I currently have implemented one such as the class, WSCheckboxEventHandler:
Option Explicit
Private WithEvents m_ole As MSForms.CheckBox
Private m_ws As Worksheet
Public Sub init(p_SourceOLE As MSForms.CheckBox, p_ws As Worksheet)
Set m_ole = p_SourceOLE
Set m_ws = p_ws
End Sub
Private Sub m_ole_Click()
Debug.Print "Checkbox click for " + m_ole.name
m_ole.Left = m_ole.Left
m_ole.Width = m_ole.Width
m_ole.Height = m_ole.Height
m_ole.Top = m_ole.Top
m_ws.Shapes(m_ole.name).ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft
m_ws.Shapes(m_ole.name).ScaleHeight 0.8, msoFalse, msoScaleFromTopLeft
End Sub
In a worksheet module, with m_WSObjectEventHandler
as a private variable, the following sets the handler up perfectly:
Set m_WSObjectEventHandler = New WSCheckboxEventHandler
m_WSObjectEventHandler.init Sheet1.chk_DraftMode, Sheet1
Basically this is a hack work around for the objects resizing visually - by calling these commands I force them to remain sized correctly. The linked question above details this problem.
However, this requires me to create a separate event handler for each type of control. I have about 7 now, so I've created a separate class which basically serves as a pseudo factory for these, passing in the worksheet, then iterating through all the ActiveX objects for it and creating the appropriate handler via an ugly select statement:
For Each mOLE In m_ws.OLEObjects
Select Case TypeName(mOLE.Object)
Case "CheckBox"
Set mCheckBoxHndlr = New WSCheckboxEventHandler
mCheckBoxHndlr.init mOLE.Object, m_ws
m_CheckBoxes.Add mCheckBoxHndlr
'etc... there are a lot of these!
Case Default
Debug.Print "Default"
End Select
Next mOLE
This lets me however have a single worksheet variable contain all the event handlers as member collections. Ugly? Yes, but it will allow better code reuse.
Question
I want to be able to implement a single event handler for all ActiveX object types (there are many, the factory type class above is going to have a huge ugly switch statement). Basically changing MSForms.CheckBox
to MSForms.Control
in the above event handler. It'd be great to not have to copy the same code into 5+ event handlers and maintain that. Not to mention avoiding the ugly select statement.
How can I refer to the control as a valid MSForms.Control
object and consequentially setup the event handler? I basically want to typecast the MSForms.CheckBox
into a MSForms.Control
object.
Alternatively, is it possible to get the MSForms.Control
object somehow? It doesn't seem to be part of the OLEObject.Object at all (I get type errors doing this).