Assign Sub to dynamically defined CommandButton

Question

I am trying to define a userform that creates a number of listboxes depending on the user. Within the procedure I define commandbuttons supposed to allow the user to take items from one listbox to another. The commandbuttons are defined as follows (within a loop - see i below):

With .Designer.Controls.Add("Forms.CommandButton.1", Name:="GroupButton1" & i)
    .Top = 40 * (i - 1) + 10
    .Left = 100
    .Width = 20
    .Height = 10
    .BackColor = RGB(220, 105, 0)
    .Caption = ">>"
    .Font.Size = 8
    .OnClick = "GroupButton1" & i & "_Click"
End With

I tried to use .OnClick and .OnAction to assign the corresponding _Click sub with no success. Any guidance would be much appreciated.

Answer 1

Command button does not have OnClick property and that is why it is not working. The way that controls are built manually with the designer is different from how they can be built dynamically.

This problem was really annoying, but happily I eventually solved it, however I would not go this route since it is hard to control the situation and the code is more complex. If you use the worksheet as the basis for your control buttons it is a lot easier to create and assign macros to them, you need to simply use .OnAction property in that case.

so follow these instruction CAREFULLY:

1- create a userform where the buttons and other controls will be built dynamically. In my example its name is UserForm1. right click on it, choose view code and paste this code exactly.

Option Explicit
Dim btnArray() As New Class1

Sub CreateButton()
    Dim i As Long
    Dim cmdBtn As MSForms.CommandButton


    i = 1

    For i = 1 To 3
        Set cmdBtn = Me.Controls.Add("Forms.CommandButton.1", "MyButton" & i)
        With cmdBtn
            .Name = "GroupButton1" & i
            .Top = 40 * (i - 1) + 10
            .Left = 100
            .Width = 50 'I changed it
            .Height = 25 'I changed it
            .Caption = ">>"
            .Font.Size = 8
            ReDim Preserve btnArray(1 To i)
            Set btnArray(i).btnEvents = cmdBtn
        End With
    Next


End Sub


Private Sub UserForm_Initialize()
    Call Me.CreateButton
End Sub

2- Create a Class Module (class module and not regular module), by default its name should be Class1. You can change it, but I did not. Paste the following code in that module:

Public WithEvents btnEvents As MSForms.CommandButton

Private Sub btnEvents_click()
    MsgBox "It worked"
End Sub

3- Now you can call UserForm1 different ways. I prefer to explicitly call them from a regular module so create a regular module, its name does not matter, but by default it will be Module1, paste this code there and when you run it, everything should work perfectly:

Sub main()
    UserForm1.Show
End Sub