Using a Variable to Reference the Name of a ComboBox in Form (And Loop)

[_Echo_2]

Hi all,

I'm working on a loop that will look-up and place values (text or numbers) into a collection of combo and text boxes in a Userform. I have 75 comboboxes and 150 textboxes in the userform (2 textboxes under each combobox). Lets say the name of the comboboxes are as follows: Cbox_1, Cbox_2, ..., Cbox_75. The names of the text boxes are: Min_1, Min_2, ..., Min_75 and Max_1, Max_2, ..., Max_75.

See the two images below for what my data/layout looks like.

I'd like the loop to operate as follows: Take the value of A1011 and put it into Cbox_1. Then offset active cell to B1011 and place that value in Min_1, then Offset to C1011 and place that value into Max_1. Then move the active cell down a column to A1012 and repeat the same except place these 3 values into Cbox_2, Min_2, and Max_2. Continue until it finds that a cell is blank in column A (in this case the loop would end when it gets to A1018 and the form is completely populated at this time).

Here is the code I tried, incorporating variables to go through the boxes in the userform, but I keep getting errors. I'm not sure if I can reference the comboboxes with variables (unless I'm doing it wrong).

Range("A1011").Select
Dim e, r, t As Integer
Dim dark, side As String
e = 0
side = ""

Set myRange = Worksheets(LookVal).Range("A1011:A1300")
r = Application.WorksheetFunction.CountA(myRange)
    
For e = 0 To r
    dark = ActiveCell.Value
        If dark <> side Then
           For t = 1 To 75
                    Cbox_t.Value = ActiveCell.Value
                    Min_t.Value = ActiveCell.Offset(0, 1).Value
                    Max_t.Value = ActiveCell.Offset(0, 2).Value
                    t = t + 1
            Next t
            Exit For
        ActiveCell.Offset(1, 0).Select
    ElseIf dark = side Then Exit For
    End If
Next e

The line of code

Cbox_t.Value = ActiveCell.Value

gives me "Run-time error 424: Object required."

If I do

Cbox_1.Value = ActiveCell.Value

then it puts "A" into the combobox without issue. I'm not sure how to get excel to cycle through the combo and textboxes in the loop and populate these values. In the end I want the form to look like when it is populated:

This is run by clicking a button in the userform, and there is a bunch of code that is before the loop I'm trying to do here that works fine so I didn't bother posting it.

Thanks for the help!

 

[mikerickson]

There is no need to move the active cell around.

For i = 1 to 75
   With Cells(1000 + i, 2)
        Userform1.Controls("Cbox_" & i).Value = .Cells(1, 1).Value
        Userform1.Controls("Max_" & i).Value = .Cells(1, 2).Value
        Userform1.Controls("Min_" & i).Value = .Cells(1, 3).Value
    End With
End With

 

[RoryA]

Assuming this code is in the userform itself, you need:

           For t = 1 To 75
                    Me.Controls("Cbox_" & t).Value = ActiveCell.Value
                    Me.Controls("Min_" & t).Value = ActiveCell.Offset(0, 1).Value
                    Me.Controls("Max_" & t).Value = ActiveCell.Offset(0, 2).Value
            Next t

Note that you do not add 1 to the value of t yourself; the Next t line will do that already.

 

[_Echo_2]

Thank you both! That was way easier and simpler than the method I was trying (no loop within a loop, less conditions to search for and less variables and strings). I pretty much eliminated all the code from the first post and simply replaced it with a combination of both of your solutions:

Dim t As Integer

For t = 1 To 75
   With Cells(1001 + t, 1)
        Me.Controls("Cbox_" & t).Value = .Cells(1, 1).Value
        Me.Controls("Min_" & t).Value = .Cells(1, 2).Value
        Me.Controls("Max_" & t).Value = .Cells(1, 3).Value
    End With
Next t

Again thanks for the quick replies and the solution! I think my problem was in calling out the boxes (I was not using Me.Controls and that was giving the "object required" error) and also the way I was using the variable for the box (not using quotes and the ampersand). This is my first time creating a userform so I'm learning...