I have a userform with a single blank multipage. At runtime additional pages are added, the number of pages depends on input
from another userform. Six frames, containing labels, textboxes and comboboxes are then added to each page.
I need to be able to use the textbox and combobox change events of these dynamically created controls to perform lookups and
calculations. Although I can name the controls at the time they are created, it is not feasible to write code specifically
for each control (I can have over 1,600 text boxes distributed over 9 pages, for example).
Having searched for some time on how to achieve this I believe using a class module is the way to go. However, how to use a
class module is just not sinking in I'm afraid.
So far I have put the following code in a Class module called
Private WithEvents mpTextBox As MSForms.TextBox
Private WithEvents mpComboBox As MSForms.ComboBox
Private Sub Class_Initialize()
Set mpTextBox = MSForms.TextBox
Set mpComboBox = MSForms.ComboBox
Private Sub mpComboBox_Change()
MsgBox "ComboBox value has been changed."
Private Sub mpTextBox_Change()
MsgBox "TextBox value has been changed."
In my UserForm module I have at the top:
Private cConttbx As clsFormEvents
Private cContcbx As clsFormEvents
Where I create the controls is the following:
For r = 1 To 7
Set cContcbx = .Controls.Add("Forms.ComboBox.1", True)
CBNum = r + ((c - 1) * 7) + (MPVal * CBCount)
With cContcbx ' Comboboxes
.Name = "ComboBox" & CBNum
.Height = 0.1 * HeightFrameRow1
.top = 0.2 * HeightFrameRow1 + ((r - 1) * 0.1 * HeightFrameRow1)
.Width = (Controls("Frame" & 2 + (MPVal * FRCount)).Width - 100) * 0.48
.left = 5 + ((c - 1) * Controls("ComboBox" & 1 + (MPVal * CBCount)).Width)
.Font.Size = 10
.TextAlign = 1
.Value = ""
When this runs I get "Compile error: Method or data member not found", so there is something I'm missing in setting
all this up.
I would really appreciate some guidance on how to use the class module to capture the change events
of any instance of a specified control (specifically textboxes and comboboxes for my purpose).
Grateful thanks in