Create a dynamic button via VBA at runtime

Question

I created a Userform, that shows a few values from the table (within textboxes) that can be edited and a command button to save those changes back into the table.

Since I use the same template for various values I decided to dynamically create the Userform at runtime. But I can't get the command button to work at all. I cross-checked with various examples (on StackOverflow and other sites, e.g. here and here), but I can't find the problem.

This is my (simplified) code. The procedure is being called by a button click event itself (CommandButton1). As a workaround, I created a static (empty) UserForm UserForm1 which I use as a starting point to create a specific configuration of the form dynamically during runtime. Essentially it means that just the form elements are created dynamically. At least for now, but that's fine since I only need one single instance of the form to be displayed at any given time. Eventually, I would like to create the form at runtime too:

Private Sub CommandButton1_Click()
  'Set Form
    UserForm1.Caption = "Test"
  'Create Form-Elements (Commandbutton)
    Dim cmdButton01 As MSForms.CommandButton
    Set cmdButton01 = UserForm1.Controls.Add("Forms.CommandButton.1", "dynCmdButton01")
    cmdButton01.Width = 50
    cmdButton01.Caption = "Save"
  'Show Form
    UserForm1.Show
  End Sub

Private Sub dynCmdButton01_click()
  MsgBox "Test"
  End Sub

Does this answer your question? Assign code to a button created dynamically — Tom Brunberg
– Tom Brunberg, Commented Aug 19, 2020 at 11:19
@TomBrunberg the missing class module was the problem, not sure if you or TJS was first to answer but feel free to add one if you like. — Albin
– Albin, Commented Aug 19, 2020 at 17:03
I support that you assign @TJS 's answer as the correct one. Cheers — Tom Brunberg
– Tom Brunberg, Commented Aug 19, 2020 at 17:05

Albin · Accepted Answer · 2020-08-20 06:03:34Z

2

You should create a class module and assign the event through it:

edited Aug 20, 2020 at 6:03

Albin

1,1471 gold badge17 silver badges46 bronze badges

answered Aug 19, 2020 at 11:11

TJS

815 bronze badges

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

1 Comment

Albin Over a year ago

the missing class module was the problem, not sure if you or Tom was first to answer?!

Collectives™ on Stack Overflow

Create a dynamic button via VBA at runtime

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related