CODEWITHSUNDEEP
vbaexcelcomboboxlistbox
Nathan Batts
Nathan Batts

Reputation: 13

ComboBox(number)_Change from generated ComboBoxes in VB

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.

Upvotes: 0

Views: 120

Answers (1)

TinMan
TinMan

Reputation: 7759

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.

enter image description here

enter image description here

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

Upvotes: 1

Related Questions