How to associate userform controls with each other?

[jplank]

This is an oversimplified version of what I am trying to do. I have a userform with 10 textboxes and 10 comboboxes. The textboxes are named Text1, Text2, ..., Text10. The comboboxes are named Combo1, Combo2, ..., Combo10. For each of the textboxes, when a value is input, I want the rowsource of the associated combobox to change. For example:

Private Sub Text1_Change()
Combo1.RowSource = Text1.value
End Sub

Private Sub Text2_Change()
Combo2.RowSource = Text2.value
End Sub

...etc
I have similar code for Text3_Change, ..., Text10_Change. Again, this is a simplification and the code is slightly more lengthy/complex. I'm wondering if there's an easier way to set this up so that the code isn't so repetitive. Is there a way to associate each textbox and combobox together so that I can write a single piece of code that says "when any textbox is updated, set the rowsource of the associated combobox to the value of it's textbox." I hope I'm articulating that well enough. I feel like this ought to be possible, but not sure how to do it.

I have crossposted this question here

 

[Norie]

You could use a class to handle the Change event of each textbox.

1 Create a new class module, name it CTextBoxes and paste this code in it.

Option Explicit

Public WithEvents TBox As MSForms.TextBox

Private Sub TBox_Change()
    TBox.Parent.Controls(TBox.Tag).RowSource = TBox.Value
End Sub

2 Add this code to the userform module.

Option Explicit
Dim TextBoxes() As CTextBoxes

Private Sub UserForm_Initialize()
Dim idx As Long

    ReDim TextBoxes(1 To 10)
    
    For idx = 1 To 10
    
        Set TextBoxes(idx) = New CTextBoxes
        
        Set TextBoxes(idx).TBox = Me.Controls("TextBox" & idx)
        
        Me.Controls("TextBox" & idx).Tag = "ComboBox" & idx
    Next idx
    
End Sub