Accessing Event Procedures of Form Controls with VBA in Excel

Question

Is there any way to define event procedures for form controls such as the case with ActiveX objects?

I have a GUI which currently adds/removes ActiveX command buttons by the user but ran into Error 91 when trying to add an item to a global collection with each button added. My best guess is because of this much hated phenomenon. With the current state of the project, my best option is to switch to using form control buttons if I am able to define their event procedures. If not, I may need to somehow save the global variables and read them back after manipulating ActiveX control objects.

Answer 1

If you just want to add buttons and capture clicks, you can assign a common macro to them, and switch what action is taken based on the name of the button (which you would set when you create it, and which can be accessed via Application.Caller in the called procedure)

Sub AddButtons()

    With ActiveSheet.Buttons.Add(100, 100, 50, 50)
        .Name = "button1"
        .OnAction = "ClickHandler"
    End With
    With ActiveSheet.Buttons.Add(200, 100, 50, 50)
        .Name = "button2"
        .OnAction = "ClickHandler"
    End With


End Sub

Sub ClickHandler()
    Dim bName As String

    bName = Application.Caller
    Select Case bName
        Case "button1": MsgBox "Clicked First button"
        Case "button2": AnotherSub 
    End Select

End Sub