Excel spreadsheet events - how to create generic M

2019-08-07 18:35发布

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

0条回答
登录 后发表回答