ComboBox(number)_Change from generated ComboBoxes in VB

Question

T he code below is what I am having some problems with. I'm pretty green to using Userforms in VB.

My goal is to create 3 ComboBoxes drawing data from the column of Vendors in the sheet "Vendor Bids" and 3 ListBoxes to select the vendor's product.

For j = 1 To 3

    Set myCombo = Frame1.Controls.Add("Forms.ComboBox.1", "ComboBox" & j)
    Set myList = Frame1.Controls.Add("Forms.ListBox.1", "ListBox" & j)

    With myList
        .Top = 18 + (150 - 84) * (j - 1)
        .Height = 34.85
        .Left = 198
        .Width = 180
        MsgBox .Name
    End With

    With myCombo
        .Top = 18 + (150 - 84) * (j - 1)
        .Height = 22.8
        .Left = 42
        .Width = 132
    End With

    Set rData = ThisWorkbook.Worksheets("VendorBids").Range("A:A").CurrentRegion
    Me.Controls("ComboBox" & j).List = rData.Offset(1).Value
    Me.Controls("ListBox" & j).ColumnCount = 1
    Me.Controls("ListBox" & j).List = rData.Offset(1, 1).Value



Next

This part works perfectly. The reason I have this coded and not made in the Userform is because I have a function to add another row of the Combo and List boxes when the user presses the commandbutton. It works perfectly as well.

The problem I am having is with ComboBox_Change(). If I create the combobox in the UserForm GUI editor then ComboBox1_Change() will work. Below is an example with what I'm trying to achieve but with all of the generated comboboxes, like ComboBox2, 3, and so on...

Private Sub ComboBox1_Change()

Me.ListBox1.ListIndex = Me.ComboBox1.ListIndex

End Sub

I apologize if I'm not very clear in my logic or explanations - this is something I'm working to improve on as a novice.

Possible duplicate of [Subscribe to events in VBA?](https://stackoverflow.com/questions/6216031/subscribe-to-events-in-vba) — 41686d6564 stands w. Palestine, Jul 02 '18 at 20:14

score 1 · Accepted Answer · answered Jul 03 '18 at 00:07

Reference:Chip Pearson - Events And Event Procedures In VBA

You will need a combination of WithEvents and RaiseEvents to handle the events of the new controls.

ComboBoxHandler:Class

Stores a reference to a single Combobox. Using WithEvents it notifies the ControlHandlerCollection when the ComboBox_Change().

Option Explicit
Public ControlHandlerCollection As VBAProject.ControlHandlerCollection
Public WithEvents ComboBox As MSForms.ComboBox

Private Sub ComboBox_Change()
    ControlHandlerCollection.ComboBoxChanged ComboBox
End Sub

ListBoxHandler:Class

Stores a reference to a single ListBox . Using WithEvents it notifies the ControlHandlerCollection when the ListBox_Change().

Option Explicit
Public ControlHandlerCollection As VBAProject.ControlHandlerCollection
Public WithEvents ListBox As MSForms.ListBox

Private Sub ListBox_Change()
    ControlHandlerCollection.ListBoxChanged ListBox
End Sub

ControlHandlerCollection:Class

Holds a collection of both ComboBoxHandlers and ListBoxHandlers whenever one of the handler class notifies it of a change it raises an event to notify the Userform of the change.

Private EventHandlers As New Collection

Public Event ComboBoxChange(ComboBox As MSForms.ComboBox)
Public Event ListBoxChange(ListBox As MSForms.ListBox)

Public Sub AddComboBox(ComboBox As MSForms.ComboBox)
    Dim ComboBoxHandler As New ComboBoxHandler
    Set ComboBoxHandler.ControlHandlerCollection = Me
    Set ComboBoxHandler.ComboBox = ComboBox
    EventHandlers.Add ComboBoxHandler
End Sub

Public Sub AddListBox(ListBox As MSForms.ListBox)
    Dim ListBoxHandler As New ListBoxHandler
    Set ListBoxHandler.ControlHandlerCollection = Me
    Set ListBoxHandler.ListBox = ListBox
    EventHandlers.Add ListBoxHandler
End Sub

Public Sub ComboBoxChanged(ComboBox As MSForms.ComboBox)
    RaiseEvent ComboBoxChange(ComboBox)
End Sub

Public Sub ListBoxChanged(ListBox As MSForms.ListBox)
     RaiseEvent ListBoxChange(ListBox)
End Sub

UserForm1:UserForm

Option Explicit
Private WithEvents ControlHandlerCollection As ControlHandlerCollection

Private Sub ControlHandlerCollection_ComboBoxChange(ComboBox As MSForms.ComboBox)
    MsgBox "Value: " & ComboBox.Value & vbNewLine & _
           "Name: " & ComboBox.Name & vbNewLine & _
           "Tag: " & ComboBox.Tag
End Sub

Private Sub ControlHandlerCollection_ListBoxChange(ListBox As MSForms.ListBox)
    MsgBox "Value: " & ListBox.Value & vbNewLine & _
           "Name: " & ListBox.Name & vbNewLine & _
           "Tag: " & ListBox.Tag
End Sub

Private Sub UserForm_Initialize()
    Set ControlHandlerCollection = New ControlHandlerCollection
End Sub

Private Sub btnAddRow_Click()
    Dim j As Long
    Dim rData As Range
    Dim myCombo As MSForms.ComboBox, myList As MSForms.ListBox
    Set rData = ThisWorkbook.Worksheets("VendorBids").Range("A:A").CurrentRegion

    For j = 1 To 3

        Set myCombo = Frame1.Controls.Add("Forms.ComboBox.1", "ComboBox" & j)
        Set myList = Frame1.Controls.Add("Forms.ListBox.1", "ListBox" & j)

        With myList
            .Top = 18 + (150 - 84) * (j - 1)
            .Height = 34.85
            .Left = 198
            .Width = 180
            .ColumnCount = 1
            .List = rData.Offset(1, 1).Value
            .Tag = rData.Offset(1, 1).Address
        End With

        With myCombo
            .Top = 18 + (150 - 84) * (j - 1)
            .Height = 22.8
            .Left = 42
            .Width = 132
            .List = rData.Offset(1).Value
            .Tag = rData.Offset(1).Address
        End With

        ControlHandlerCollection.AddComboBox myCombo
        ControlHandlerCollection.AddListBox myList
    Next
End Sub

Thank you! It's working 100% so far. Would you mind explaining more or pointing to some resources to learn more about WithEvents, RaiseEvents, and how collections work? I truly appreciate your help. — Nathan Batts, Jul 03 '18 at 19:06
[VBA - Handling events from an indefinite number of event sources](https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-handling-events-indefinite-sources) explains it pretty well. — TinMan, Jul 03 '18 at 19:27
The is used collection to store references to the control handlers. Without the collection the control handlers would be cleaned up by the VBA Garbage Collector at the end of the subroutine. — TinMan, Jul 03 '18 at 19:36
`WithEvents` hookes the class into the control's events that are exposed through Com. You can also create your own class events which will notify any class or userform that is monitoring your class's events using `WithEvents`. — TinMan, Jul 03 '18 at 19:39
Ok so the ComboBox_Change() works for me perfectly. Now to get it what I want it to do. It has the all of the data of the ComboBox that was changed, specifically the name. The name is myCombo(number). It doesn’t have anything about which listbox is tied to it. So, to make it dynamic, I think i will 1. Parse name of myCombo(number) to only get the number. 2. Build a variable named (myList + number) where the number was parsed from the myCombo name. 3. Set new variable of myList(number).ListIndex = ComboBox.ListIndex Is there a more efficient or cleaner way to achieve this? — Nathan Batts, Jul 03 '18 at 20:01

ComboBox(number)_Change from generated ComboBoxes in VB

1 Answers1

ComboBoxHandler:Class

ListBoxHandler:Class

ControlHandlerCollection:Class

UserForm1:UserForm