Dynamic created User-Form, with 2 dependent Combo-

Question

I am trying to create a dynamic User_form, where all the Controls are created at Run-Time.

I have 2 array of Combo-Boxes, first array of Combo-Boxes is "Catgeory" (CatCBArr) , and the second array of Combo-Boxes is "Item" (ItemCBArr).

I want, that once I select a value from the first Combo-Box of "Category", let's say CatCBArr(0), that only the related Items in ItemCBArr(0) will be displayed.

Issue: I can't figure out how to modify the second Combo-box (ItemCBArr(0)) according to the value selected in the first Combo-box (CatCBArr(0))

User_Form Code (relevant section)

Option Explicit

Dim ItemsNumofRows As Long    
Dim QtyTB As MSForms.TextBox
Dim CatCB As MSForms.ComboBox
Dim ItemCB As MSForms.ComboBox

Dim Key As Variant

' dynamic Form controls (related to new Classes)
Dim CatCBArr()                     As New cComboBox
Dim ItemCBArr()                    As New cComboBox    
Dim QtyTBArr()                     As New cTextBox         

Private Sub UserForm_Initialize()

' reset flags
ItemsNumofRows = 5
TasksNamesUpd = False
TasksColUpd = False

ItemsRows_ControlsInit '<-- upload all Controls at run-time
Check_FormHeight

End Sub

'======================================================

Private Sub ItemsRows_ControlsInit()

For ItemRow = 0 To ItemsNumofRows

    ' add Category Combo-boxes
    Set CatCB = Me.Controls.Add("Forms.ComboBox.1", "Cb" & ItemRow, True)
    With CatCB
        ' loop through Dictionay items (view category)
        For Each Key In Dict.Keys
            .AddItem Key
        Next Key

        .SpecialEffect = fmSpecialEffectSunken
        .Left = 40
        .Width = 100
        .Height = 18
        .Top = 54 + 20 * ItemRow

        ReDim Preserve CatCBArr(0 To ItemRow)
        Set CatCBArr(ItemRow).ComboBoxEvents = CatCB
    End With

    ' add Item Combo-boxes
    Set ItemCB = Me.Controls.Add("Forms.ComboBox.1", "Cb_" & ItemRow, True)
    With ItemCB

        .SpecialEffect = fmSpecialEffectSunken
        .Left = 160
        .Width = 100
        .Height = 18
        .Top = 54 + 20 * ItemRow

        ReDim Preserve ItemCBArr(0 To ItemRow)
        Set ItemCBArr(ItemRow).ComboBoxEvents = ItemCB
    End With    
Next ItemRow

End Sub

cComboBox Class Code

Public WithEvents ComboBoxEvents As MSForms.ComboBox

Private Sub ComboBoxEvents_Change()

Dim CBIndex                            As Long

' get for ID number (row number), from third character in String Name.
' e.g "Cb1" will result 1)
CBIndex = CInt(Mid(ComboBoxEvents.Name, 3))

' ??? How do I get the Value, and update the second combo-box Items

Select Case ComboBoxEvents.Value


End Select

End Sub

GUI User_Form screen-shot

Answer 1

Alright, here's the basics. Your class cCombobox I replicated as follows:

Private WithEvents ComboBoxEvents As MsForms.ComboBox
Private Sub ComboBoxEvents_Change()
    Select Case ComboBoxEvents.value
        Case "1":
            UserForm1.DependentBox.Clear
            UserForm1.DependentBox.AddItem "3"
            UserForm1.DependentBox.AddItem "4"
        Case "2":
            UserForm1.DependentBox.Clear
            UserForm1.DependentBox.AddItem "5"
            UserForm1.DependentBox.AddItem "6"
        Case Default:
            'Do Nothing
    End Select
End Sub
Public Property Let box(value As MsForms.ComboBox)
    Set ComboBoxEvents = value
End Property
Public Property Get box() As MsForms.ComboBox
    Set box = ComboBoxEvents
End Property

Next, I created a UserForm1 that adds 2 comboboxes, one of which I add to a local variable of type cComboBox.

Public DependentBox As MsForms.ComboBox
Private InitialBox As cComboBox
Private Sub UserForm_Initialize()
    Dim cBox As MsForms.ComboBox
    Set InitialBox = New cComboBox

    Set cBox = Me.Controls.Add("Forms.ComboBox.1", "initial", True)
    With cBox
        .Left = 6
        .Width = 100
        .Height = 25
        .Top = 6
        .AddItem "1"
        .AddItem "2"
    End With
    InitialBox.box = cBox

    Set DependentBox = Me.Controls.Add("Forms.ComboBox.1", "dependent", True)
    With DependentBox
        .Top = 6
        .Left = 126
        .Height = 25
        .Width = 100
    End With
End Sub

Even though this works, the above approach is not very clean, since your class is not self-contained - It has to be aware of the UserForm. A better way would be to link the boxes in the class already and then just pass them from the Userform when you initialize your arrays of controls. Then it would be:

cComboBox class:

Private WithEvents p_ComboBoxEvents As MSForms.ComboBox
Private p_DependBox As MSForms.ComboBox
Private Sub p_ComboBoxEvents_Change()
    Select Case p_ComboBoxEvents.value
        Case "1":
            p_DependBox.Clear
            p_DependBox.AddItem "3"
            p_DependBox.AddItem "4"
        Case "2":
            p_DependBox.Clear
            p_DependBox.AddItem "5"
            p_DependBox.AddItem "6"
        Case Default:
            'Do Nothing
    End Select
End Sub
Public Property Let TriggerBox(value As MSForms.ComboBox)
    Set p_ComboBoxEvents = value
End Property
Public Property Get TriggerBox() As MSForms.ComboBox
    Set TriggerBox = p_ComboBoxEvents
End Property
Public Property Let DependBox(value As MSForms.ComboBox)
    Set p_DependBox = value
End Property
Public Property Get DependBox() As MSForms.ComboBox
    Set DependBox = p_DependBox
End Property

Here you see you already link the boxes in a self-contained class. In the event handler you could create a lookup for the values, etc. Then in the UserForm1 code you initialize them as follows:

Option Explicit
Private LinkedComboBox As cComboBox
Private Sub UserForm_Initialize()
    Dim cBox As MSForms.ComboBox
    Set LinkedComboBox = New cComboBox

    Set cBox = Me.Controls.Add("Forms.ComboBox.1", "initial", True)
    With cBox
        .Left = 6
        .Width = 100
        .Height = 25
        .Top = 6
        .AddItem "1"
        .AddItem "2"
    End With
    LinkedComboBox.TriggerBox = cBox

    Set cBox = Me.Controls.Add("Forms.ComboBox.1", "dependent", True)
    With cBox
        .Top = 6
        .Left = 126
        .Height = 25
        .Width = 100
    End With
    LinkedComboBox.DependBox = cBox
End Sub

EDIT: Based on the fact that it needs to be an array, you can amend the userform as follows:

Option Explicit
Private LinkedComboBox(0 To 4) As cComboBOx
Private Sub UserForm_Initialize()
    Dim cBox As MSForms.ComboBox
    Dim i As Integer

    For i = 0 To 4
        Set LinkedComboBox(i) = New cComboBOx
        Set cBox = Me.Controls.Add("Forms.ComboBox.1", "initial", True)
        With cBox
            .Left = 6
            .Width = 100
            .Height = 25
            .Top = 6 + (i * 25)
            .AddItem "1"
            .AddItem "2"
        End With
        LinkedComboBox(i).TriggerBox = cBox

        Set cBox = Me.Controls.Add("Forms.ComboBox.1", "dependent", True)
        With cBox
            .Top = 6 + (i * 25)
            .Left = 126
            .Height = 25
            .Width = 100
        End With
        LinkedComboBox(i).DependBox = cBox
    Next i

End Sub

In the array you can access each box as LinkedComboBox(i).DependBox and LinkedComboBox(i).TriggerBox. You won't need the two seperate arrays anymore, since everything is already contained in this LinkedComboBox array