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.

1 Answer
1

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.

WithEvents

RaiseEvents

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

ControlHandlerCollection

ComboBox_Change()

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

Private Sub ComboBox_Change()
ControlHandlerCollection.ComboBoxChanged ComboBox
End Sub


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

ControlHandlerCollection

ListBox_Change()

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

Private Sub ListBox_Change()
ControlHandlerCollection.ListBoxChanged ListBox
End Sub


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.

ComboBoxHandlers

ListBoxHandlers

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


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


WithEvents

WithEvents

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.