Class for ComboBox Event created at RunTime

[JaberwokWSA]

My first post!!! Woo hoo!!

I have a UserForm that contains checkboxes and one frame "FRAME_Main".

When I click a check box (say a group of products from a catalog), I want the frame to populate with available items in that group. I create a FRAME_xx (xx it a number 01 to 99), and within FRAME_xx, I create a ComboBox "CB_Item", a TextBox "TB_Item", and other objects like Radio Buttons. The ComboBox is populated with a two column array with Catalog Number and Item Name.

What I need is an event, so that when I change any ComboBox, the TextBox is updated with the Item Name from the ComboBox, and other controls also change.

My problem is that I can't seem to get the class I created for the ComboBox to work for all ComboBoxes. The Change event is triggerred only for the last ComboBox I create.

Here is my code.

Class Module "C_MultiComboBox"

Option Explicit

Public WithEvents cv_MultiComboBox As MSForms.ComboBox
 
Private Sub cv_MultiComboBox_Change()
    MsgBox "Combo Box with content " & cv_MultiComboBox & " has changed"
End Sub

Public Property Set ComboBoxObj(vv_Obj As MSForms.ComboBox)
    Set cv_MultiComboBox = vv_Obj
End Property

UserForm1 (contains 1 CheckBox "CHK_Item1" and 1 Frame "FRAME_Main"

Option Explicit

Dim V_MultiComboBoxES As Collection


Private Sub CHK_Item1_Click()
    Dim v_Row As Integer
        v_Row = 1
    ReDim v_Bams(1, 2) As String
    ' Position Frame Main
        With FRAME_Main
            .Height = 200
            .Left = 100
            .Top = 10
            .Width = 600
        End With
    ' Create Catalog Item 1
        ReDim v_Items(1 To 2, 1 To 2) As String
            v_Items(1, 1) = "12345"
                v_Items(1, 2) = "Widget"
            v_Items(2, 1) = "67890"
                v_Items(2, 2) = "Thing-a-ma-bob"
        Dim v_Frame01 As Frame
        Set v_Frame01 = FUNCT_ItemList(v_Row, v_Items)
        v_Row = v_Row + 1
    ' Create Catalog Item 2
        ReDim v_Items(1 To 2, 1 To 2) As String
            v_Items(1, 1) = "98765"
                v_Items(1, 2) = "Whatcha-ma-callit"
            v_Items(2, 1) = "43210"
                v_Items(2, 2) = "Doo-hickey"
        Dim v_Frame02 As Frame
        Set v_Frame02 = FUNCT_ItemList(v_Row, v_Items)
        v_Row = v_Row + 1
    ' Etc ....
End Sub

Function FUNCT_ItemList(vv_Row As Integer, vv_Items() As String) As Frame
    Dim v_FrameNew As Frame
    Set v_FrameNew = Me.Controls("FRAME_Main").Add("Forms.Frame.1", "FRAME_Item" & Format(vv_Row, "00"))
        With v_FrameNew
            .Height = 20
            .Left = 10
            .Top = vv_Row * 20
            .Width = 530
        End With
    ' ***************************************************************************
    ' This attempts to add events.  Only works for the last one created.
        ' Variables
            Dim v_MultiComboBoxEvent As C_MultiComboBox
            Dim v_ComboBox As MSForms.ComboBox
        ' Collection to store C_MultiComboBox objects
            Set V_MultiComboBoxES = New Collection
        ' Create the desired combo boxes
            Set v_ComboBox = v_FrameNew.Controls.Add("Forms.ComboBox.1", "CB_Item")
        ' Create new object to handle controls events
            Set v_MultiComboBoxEvent = New C_MultiComboBox
        ' Set reference to objects control
            Set v_MultiComboBoxEvent.ComboBoxObj = v_ComboBox
        ' Add object to collection to allow multiple event handlers
            V_MultiComboBoxES.Add v_MultiComboBoxEvent, CStr(V_MultiComboBoxES.Count + 1)
    ' ***************************************************************************
    ' Set ComboBox properties
        With v_FrameNew!CB_Item
            .Height = 18
            .Left = 20
            .Top = 1
            .Width = 80
            .ColumnCount = 2
            .ListWidth = 500
            .ColumnWidths = 75
            .List = vv_Items()
        End With
    v_FrameNew.Controls.Add "Forms.Label.1", "TB_Item"
        With v_FrameNew!TB_Item
            .Height = 18
            .Left = 100
            .Top = 3
            .Width = 290
            .Caption = "Set by Combo List Change Event"
        End With
    ' Add Other Controls, such as radio buttons and other combo boxes
    Set FUNCT_ItemList = v_FrameNew
End Function

I'm using Excel 2007 on Windows 7

Where am I going wrong?

Thanks

 

[Norie]

Every time you call FUNCT_ItemList you are creating a new collection.

Try creating the new collection before you add any of the controls and add the controls to the collection as you create them.

 

[JaberwokWSA]

Re: Class for ComboBox Event created at RunTime - SOLVED

Thank you, Norie.

I moved the New Collection line into the CHK_Item1_Click procedure, and everything works perfectly.

Simple fix, but I stared at it so long, I never saw it.


SOLVED.