VBA Excel Userform EventHandler for dynamically generated controls not working as expected

Question

I'm working in Excel 2013, designing a Userform with controls created at run-time. I'm having trouble making a function that will fire on the change event of the dynamically created controls.

The controls are contained in a separate class module that creates them and manages them. I want to add a function that fires on the change event of a combo-box, so I have it declared WithEvents:

Private WithEvents myComboBox As MSForms.ComboBox
... other controls and variable declarations...

I have a function that is passed the frame that I want the controls to be in so that I can create all the components from within the class.

Sub initialize(myID As String, myFrame As MSForms.Frame, Left As Double, Top As Double)
    ...
    Set myComboBox = myFrame.Controls.Add("Forms.ComboBox.1", myID & "_comboBox")
    ...
End Sub

This all works, and the ComboBox is created and properties changed though myComboBox successfully change how the ComboBox appears.

Because myComboBox is declared WithEvents, I can find myComboBox_Change as an option in the drop-down menus and put it in the module:

Private Sub myComboBox_Change()
    MsgBox ("Change Event Fired")
End Sub

But this function will not run, and I can't figure out why. A messagebox will not appear when a change is made, and breakpoint put in this function will not stop any code from running. What am I doing wrong?

I can't check it myself nor sure to remember correctly but ... try "Public WithEvents myComboBox As MSForms.ComboBox" instead of "Private..." — user3598756
– user3598756, Commented Aug 26, 2016 at 21:21
Thank you for the suggestion, but it did not make a difference. — SudoNim
– SudoNim, Commented Aug 26, 2016 at 21:25
Do you have more than one? What's the purpose of dynamically assigning the name as myID & "_comboBox"? — Comintern
– Comintern, Commented Aug 26, 2016 at 21:27
are you adding myComboBox to a collection? Better if you ca post full code for intialize. — cyboashu
– cyboashu, Commented Aug 26, 2016 at 21:35
While looking into this, I figured it out. Thank you! myComboBox is not directly in a collection. I thought the class the combo box was in was in a collection, but that collection was only declared within a function in the userform. Once I declared the collection of classes containing the combobox for a scope of the entire userform, the event handling worked. — SudoNim
– SudoNim, Commented Aug 26, 2016 at 21:43

SudoNim · Accepted Answer · 2016-08-26 21:50:03Z

2

With the help of some probing questions from cyboashu, I figured out what was wrong. The class containing myComboBox was not held by a variable declared for the scope of the entire userform. After the function creating it completed, the components were still in myFrame, but the functions to be fired on events were forgotten

answered Aug 26, 2016 at 21:50

SudoNim

254 bronze badges

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

2 Comments

user3598756 Over a year ago

would it work with Private WithEvents myComboBox As MSForms.ComboBox?

SudoNim Over a year ago

Yes, once the class was declared in the right scope, it did not matter whether the component was public or private. Both ways worked.

Collectives™ on Stack Overflow

VBA Excel Userform EventHandler for dynamically generated controls not working as expected

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related