Can't add events to dynamically created buttons in VBA userform

Question

Simply put, I've tried following the example found here : How to add events to Controls created at runtime in Excel with VBA but when I click my button nothing happens.

The thing is that I don't create lots of buttons at once, a new button is added everytime the user clicks a certain pre added button.

Code for the button creation :

'Finally the delete button
Dim newb As MSForms.CommandButton
'thisQuant is a var keeping track of how many buttons there are
Set newb = FProducts.Controls.Add("Forms.CommandButton.1", "del" & thisQuant, False)
Dim Button As New Class1
Set Button.delButton = newb

And the new Class as stated in the example :

Public WithEvents delButton As MSForms.CommandButton

Private Sub delButton_Click()
    MsgBox "test"
    'quantProd = CInt(NewNota.ProductQuant.Caption)
End Sub

I come from python and VBA is extremely confusing.

probably you should show more of your code in a [mcve]. THe other solution should work, so your first approach should be to copy / implement that *exactly* as-is, and see if that works. Do that in a new workbook if needed. I notice that in the other solution, it's creating a button *array* in the form's constructor: `Dim ButArray() As New Class2` (where `Class2` is the class module for your button event class. I seem to recall you need to keep these buttons all in memory like that. Haven't tested this in a long time though. — David Zemens, Sep 08 '17 at 22:25
Most common problem doing this is forgetting to declare your Class1 instance as Global (ie. at the top of the module, not in a Sub or Function), so it goes out of scope and is destroyed as soon as the code creating the button exits. — Tim Williams, Sep 08 '17 at 22:32
@TimWilliams Yup, that was it, thanks man. I just now realized you can make declarations in a module. If you want to post a full answer I will rep you — Mojimi, Sep 08 '17 at 22:40

Tim Williams · Accepted Answer · 2017-09-09T02:40:31.710

The most common mistake made when doing this type of thing is forgetting to declare your Class1 instance - or your array/collection of instances - as Global (ie. at the top of the module, not in a Sub or Function).

If you don't do this then your custom class instance goes out of scope and is destroyed as soon as the code creating the button exits.

Dim Button As Class1

Sub CreateButton()
    Dim newb As MSForms.CommandButton

    '...

    'thisQuant is a var keeping track of how many buttons there are
    Set newb = FProducts.Controls.Add("Forms.CommandButton.1", "del" & thisQuant, False)

    Set Button = New Class1
    Set Button.delButton = newb

    '...

End Sub

Can't add events to dynamically created buttons in VBA userform

1 Answers1