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

stackoverflow.com/questions/10592641/…

Siddharth Rout
– Siddharth Rout

2012-05-31 20:20:00 +00:00
Commented May 31, 2012 at 20:20 — Siddharth Rout
– Siddharth Rout, Commented May 31, 2012 at 20:20

Stefan · Accepted Answer · 2012-05-31 20:12:30Z

2

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.

answered May 31, 2012 at 20:12

Stefan

1,1017 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related