excel - Class for Custom Button Event Handling

Question

1) I have a Form with some buttons (its in Access, but I guess it applies for Excel as well).

2) I have a custom class that helps me debug that form (and future forms that I may add).

The class simply logs when form events fire, such as loaded, unloaded, dirty, exited.

I'd like that class to have the capability to log when buttons are clicked.

I know this can be done by using a standard module, and loading a public collection there. Or by directly using the form's events. Or by storing in a collection behind the form.

But I would like, if possible, to encapsulate it all in my debugging class. Then its a simple two lines added to the Form_Load event of each new form I add.

My simplified attempt below is only capturing the event for the last button that gets added in the class collection, ie. Button3.

TestButtons (A Form with Button1, Button2, & Button3)

Private Buttons As CButtons

Private Sub Form_Load()
    Set Buttons = New CButtons
    Buttons.LoadButtons Me
End Sub

CButtons (Class):

Public WithEvents btn As Access.CommandButton
Private AllButtons As Collection

Const MODE_DEBUG As Boolean = True

Public Sub LoadButtons(ByRef TheForm As Access.Form)

    Dim ctl As Control

    Set AllButtons = New Collection
    For Each ctl In TheForm.Controls
        If ctl.ControlType = acCommandButton Then
            Set btn = ctl
            btn.OnClick = "[Event Procedure]"
            AllButtons.Add btn
        End If
    Next ctl

End Sub

Private Sub btn_Click()
    If MODE_DEBUG Then debug.print btn.Name & "_Click"
End Sub

Wondering if anyone's got any advice, thanks!

Answer 1

You can't handle events from a collection. The easiest solution is to use a separate class to handle the button events, make a collection of those classes in your multiple buttons handler, and pass the button from the class handling the single button to the class handling multiple ones on an event.

Class CSingleButton

Public buttonsHandler As CButtons
Public WithEvents btn As Access.CommandButton

Private Sub btn_Click()
    buttonsHandler.HandleClick btn
End Sub

Class CButtons

Private ButtonHandlers As Collection

Const MODE_DEBUG As Boolean = True

Public Sub LoadButtons(ByRef TheForm As Access.Form)

    Dim ctl As Control
    Dim btnHandler As CSingleButton
    Set ButtonHandlers = New Collection
    For Each ctl In TheForm.Controls
        If ctl.ControlType = acCommandButton Then
            Set btnHandler = New CSingleButton
            Set btnHandler.btn = ctl
            Set btnHandler.buttonsHandler = Me
            ctl.OnClick = "[Event Procedure]"
            ButtonHandlers.Add btnHandler
        End If
    Next ctl

End Sub

Public Sub HandleClick(btn As Access.CommandButton)
    If MODE_DEBUG Then debug.print btn.Name & "_Click"
End Sub