Adding Click Behaviour to Dynamically Created Form Control Button with VBA in Excel

Question

As a follow-up to this question, where a userform allows the creation of x # of tabs in a multipage during runtime, each with the same controls, I am wondering how to set the .OnClick behaviour of the buttons. I am trying to achieve this with the following code:

For Each ctl In MultiPage1.Pages(NumSegs - 1).Controls
    If TypeOf ctl Is MSForms.CommandButton Then
        ctl.Name = "Segment" & NumSegs & "Button"
        ctl.OnClick = "Span_Form_Click_Handler"
    End If
Next

But of course there doesn't seem to be a .OnClick method...

http://stackoverflow.com/questions/10592641/assign-event-handlers-to-controls-on-user-form-created-dynamically-in-vba — Siddharth Rout, May 31 '12 at 20:20

score 2 · Accepted Answer · answered May 31 '12 at 20:12

You define a variable with events. A minimal example: UserForm + CommandButton on it. Code in UserForm Module:

Public WithEvents btn As MSForms.CommandButton

Private Sub btn_Click()
    MsgBox "Bla"
End Sub

Private Sub UserForm_Click()
    Set btn = CommandButton1
End Sub

After a click in the form, the button CommandButton gets the sub btn_Click assigned.

To be able to call everytime the same sub, you need to place the sub and the withevents variable into a class and create an instance of this class for each CommandButton you find.

Adding Click Behaviour to Dynamically Created Form Control Button with VBA in Excel

1 Answers1