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.

Ibo · Accepted Answer · 2018-07-25 20:13:19Z

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

edited Jul 25, 2018 at 20:13

answered Jul 25, 2018 at 18:12

Ibo

4,3197 gold badges51 silver badges75 bronze badges

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

8 Comments

user3487128 Over a year ago

Thanks for your reply. I made a mistake when pasting the piece of code. I actually tried to call the sub, as "GroupButton11_Click" with no success. I updated this in the original question. Thanks!

Ibo Over a year ago

what error do you get? error number description etc

user3487128 Over a year ago

I get Run-time error '438': Object doesn't support this property or method. Thanks much

user3487128 Over a year ago

I defined a toy sub just for testing as follows: Sub GroupButton11_Click() MsgBox "It's called!" End Sub (for each)

Ibo Over a year ago

I edited the answer, put a lota time, but fortunately it worked, please accept the answer and upvote it if you are happy :)

|

Collectives™ on Stack Overflow

Assign Sub to dynamically defined CommandButton

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related