Dynamic Userforms,

[Derek P.]

Is it possibly to create dynamic user forms(UF) that still have the ability
to have event driven logic?

Here is a simple example.
Non dynamic uf structure.
You have a UF (Non_Dynamic_UF) that you create with two combo boxes, CBX1
and CBX2. The contents of CBX2 are dependent on CBX1, such that.
CBX1 = Fruit, CBX2 has list Apple, Bannana, Orange
CBX1 = Vegitable, CBX2 has Corn, Potato, Broccoli

In the Non_Dynamic_UF section, I would create a function so that whenever
CBX1 changes value, CBX2 has its list re-defined.

Private Sub CBX1_Change()
'Code to manipulate CBX2 item list implemented here.
End Sub

NOW I want to re-create this same functionality in a dynamic UF where i add
these two combo boxes with code. Using the (UserForm).controls
collection/object, I know how to add both of the combo box controls.

THE QUESTION IS, can I also dynamically add in the EVENT DRIVEN logic that
updates CBX2's list items whenever CBX1 changes that is demonstrated in the
non dynamic uf example above?

IF IT CAN BE DONE,.... how does one go about doing it?

If it can't be done,....dang it!

Thanks everyone.

 

[Dave Peterson]

You can do it, but I've always found it much, much easier to add all the
controls I want with all the event procedures that I want and just use the
..visible property to hide/show the controls when I want them hidden or shown.

 

[dbKemp]

Is it possibly to create dynamic user forms(UF) that still have the ability
to have event driven logic?

Here is a simple example.
Non dynamic uf structure.
You have a UF (Non_Dynamic_UF) that you create with two combo boxes, CBX1
and CBX2. The contents of CBX2 are dependent on CBX1, such that.
CBX1 = Fruit, CBX2 has list Apple, Bannana, Orange
CBX1 = Vegitable, CBX2 has Corn, Potato, Broccoli

In the Non_Dynamic_UF section, I would create a function so that whenever
CBX1 changes value, CBX2 has its list re-defined.

Private Sub CBX1_Change()
'Code to manipulate CBX2 item list implemented here.
End Sub

NOW I want to re-create this same functionality in a dynamic UF where i add
these two combo boxes with code. Using the (UserForm).controls
collection/object, I know how to add both of the combo box controls.

THE QUESTION IS, can I also dynamically add in the EVENT DRIVEN logic that
updates CBX2's list items whenever CBX1 changes that is demonstrated in the
non dynamic uf example above?

IF IT CAN BE DONE,.... how does one go about doing it?

If it can't be done,....dang it!

Thanks everyone.

I have had the need to do similar things.... here is how I would go
about it.

In user form...
Option Explicit

Private mComboEvents As New cComboBoxEventHandler

Private Sub UserForm_Initialize()
Dim oCBX1 As MSForms.ComboBox
Dim oCBX2 As MSForms.ComboBox

Set oCBX1 = Me.Controls.Add("Forms.Combobox.1", "cbxFirst", True)
With oCBX1
.Left = 10
.Top = 10
.AddItem "Vegetable"
.AddItem "Fruit"
End With
Set oCBX2 = Me.Controls.Add("Forms.Combobox.1", "cbxSecond", True)
With oCBX2
.Left = 100
.Top = 10
End With
mComboEvents.Create oCBX1, oCBX2
End Sub

In a Class module called cComboBoxEventHandler
Option Explicit

Private WithEvents mCBX1 As MSForms.ComboBox
Private mCBX2 As MSForms.ComboBox

Public Sub Create(ByRef CBX1 As MSForms.ComboBox, ByRef CBX2 As
MSForms.ComboBox)
Set mCBX1 = CBX1
Set mCBX2 = CBX2
End Sub


Private Sub Class_Terminate()
Set mCBX1 = Nothing
Set mCBX2 = Nothing
End Sub

Private Sub mCBX1_Change()
mCBX2.Clear
If mCBX1.Value = "Fruit" Then
mCBX2.AddItem "Apple"
mCBX2.AddItem "Bannana"
mCBX2.AddItem "Orange"
ElseIf mCBX1.Value = "Vegetable" Then
mCBX2.AddItem "Corn"
mCBX2.AddItem "Potato"
mCBX2.AddItem "Broccoli"
End If

End Sub

 

[Derek P.]

Thanks Dave, but this doesn't help that much.

I'm trying to get away from this type of programming to allow the overall
size of the project to be smaller by using a single userform. Text requires
smaller amounts of memory than do full defined userforms.

I see dbKemp has a good example, and I'm going to give that a try.

Derek P.

 

[Derek P.]

Thank you dbKemp,

This looks like the exact answer I was looking for. I knew it had to exist
but digging to find the answer can absorb hours. You summed it up for me
rather quickly and concisely. That is greatly appriciated.

Thanks again.