Adding Code to a Command Button that was dynamically added to a frame in a user code

Question

Hi,

This is a vba question. I found the question: "Adding controls to a frame in an Excel userform with VBA" and used its method to add commandbuttons to my frame in my user form. Since I added four commandbuttons, I ran the code in a loop, and used the

 With ...
      .Name = "commandbutton" & x

to give each command button its own name. I included in my code macros for each commandbutton (since I renamed them I know what the name of each cb is) but when I click on the button, nothing happens.

Alternatively, if someone could explain to me how use code to place controls on a form into a frame, I could solve my problem a different way.

Thanks,

Michael

Answer 1

It seems that events are only handled for object variables which were declared when the VBA program is compiled (i.e. at design time.) If you dynamically create objects in the program (i.e. at run-time) then you need to assign references to those objects to appropriate object variables which have already been declared.

As per your example, in the module which relates to your UserForm, you would need to have:

Public WithEvents btn1 As MSForms.CommandButton
Public WithEvents btn2 As MSForms.CommandButton
Public WithEvents btn3 As MSForms.CommandButton
Public WithEvents btn4 As MSForms.CommandButton

(The MSForms prefix to the variable type might not be strictly necessary)

then in your With statement to create the controls you would need to assign each control to one of the variables which ends up being fairly messy.

Better solutions:

• create the maximum number of possible buttons at design time and just toggle the Visible property to show the correct ones
• if the function of each button would be determined at run-time then use some property of the CommandButton object to determine this (e.g. Caption property, Tag property) and then have the event handler for that button call the appropriate function based on that property
• change the UI and use a ListBox or ComboBox instead of buttons