Search code examples
vbaruntimeuserform

Create a dynamic button via VBA at runtime

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
Solution

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