How to use ComboBox_Change() on a combo box created dynamically by a class - VBA

Question

I have a UserForm with a MultiPage (MP1). This MultiPage is only composed of one page. On this page I have a CommandButton (CB1) and a TextBox (TB1) in which I enter a value (for example 1) as shown bellow:

By entering the value in the TB1 and by clicking CB1 I create a new TextBox (here just one named TB21) and a new CommandButton (CB2) as shown bellow:

By clicking CB2 I create a number of new pages on MP1 equal to the number entered in TB1 (here 1) and I assign the name entered in the TextBox TB21 to the new page created. On this new page I also create a new MultiPage (MP2) and pages linked to it. On the first page of MP2 I have also 2 ComboBoxes. In the first ComboBox I have two choices (let's say A and B). If A is selected, I display a list of data related to A in the second ComboBox. Same idea if B is selected in the first ComboBox but different values are displayed in the second ComboBox.

In my case, the CB2 from my MP1 uses a class to create the new page in MP1 and the MP2 linked to it with all the needed forms (here the two ComboBoxes). Here is the code from the UserForm:

Dim cmdArray() As New Classe3

Private Sub CB1_Click()

    Set CB2 = Me.MultiPage1.Pages(0).Add("Forms.CommandButton.1")

    ReDim Preserve cmdArray(1 To 1)
    Set cmdArray(1).CmdEvents = CB2 'Assign the macro the the new CommandButton
    Set cmdArray(1).frm = Me

    Set command_button = Nothing

End Sub

What I want to do now, when I run my UserForm, is to have a link between the two ComboBoxes on my first page from my MP2 so I can displayed a list of object in the ComboBox 2 related to the choice from the ComboBox 1.

I know that I can use the Sub ComboBox_Change() but in my case the two ComboBoxes are created dynamically by using a class and I don't know how I can affect an event on my dynamically created comboboxes.

Here is a part of the code in my class named Classe3:

Option Explicit
Public WithEvents CmdEvents As MSForms.CommandButton
Public frm As Object

Private Sub CmdEvents_Click()

    'Get the number entered in TB1
    TB1_value = frm.MultiPage1.Pages(0).UserForm_TB1.Value 'UserForm_TB1 is the name of the first TextBox

    'Create the new pages on MP1
    For i = 1 To TB1_value

    'Add a new page 
    Set multi_page = frm.MultiPage1.Pages.Add("page" + CStr(i), i)

        'Set the name
        With multi_page

            .Caption = frm.MultiPage1.Pages(0).Controls("UserForm_TB2" + CStr(i)).Value 'Get the name from each TextBox created dynamically

        End With

        'Add a new MultiPage on the page created   
        Set cntrl = multi_page.Controls.Add("Forms.MultiPage.1", "MP2", True)

        'Create the first combo box with two choices
        Set combobox1 = cntrl.Pages(0).Controls.Add("Forms.combobox.1", True)

        combobox1.AddItem "A" 'ListIndex = 0
        combobox1.AddItem "B" 'ListIndex = 1

        'Create the second combo box    
        Set combobox2 = cntrl.Pages(0).Controls.Add("Forms.combobox.1", True)

    Next i

End Sub

Here is what I'm trying to do but it doesn't work because comboxbox1 & 2 are created dynamically. If you have any idea it will help me a lot, thank you!

Sub combobox2_Change()

    If combobox1.Text = "A" Then

        For Each listA_ID In Worksheet1.Range("listA")
            With Me.Controls("combobox2")
                .AddItem listA_ID.Value
            End With
        Next listA_ID

    ElseIf combobox1.Text = "B" Then

        For Each listB_ID In Worksheet2.Range("listB")
            With Me.Controls("combobox2")
                .AddItem listB_ID.Value
            End With
        Next listB_ID

    End If

End Sub

How to use ComboBox_Change() on a combo box created dynamically by a class - VBA

0 Answers