1

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

Community
  • 1
  • 1
enderland
  • 13,825
  • 17
  • 98
  • 152
  • I don't think it's possible in VBA to do this. Even though you can write `Private WithEvents m_ole As MSForms.Control` in your event handler class, it's not possible to actually assign a control to that variable. If you google "WithEvents MSForms.Control" you can find a few previous discussions along these lines. – Tim Williams Jan 30 '14 at 22:09
  • @TimWilliams it also turns out that `MSForms.Textbox` doesn't have a `Click` event (it does have a `MouseDown` event though). So even if I did want to do this I don't think it will work. Which is really unfortunate all things considered... – enderland Jan 31 '14 at 19:16

0 Answers0