CODEWITHSUNDEEP
excelvbaevent-handlinguserform
SudoNim
SudoNim

Reputation: 25

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

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?

Upvotes: 0

Views: 509

Answers (1)

SudoNim
SudoNim

Reputation: 25

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

Upvotes: 2

Related Questions