Control Arrays

**gfrantsen1961** · 02-12-2007, 11:50 AM

Hello!
I know that VBA doesn't support control arrays, but I was wondering if anybody out there had found a fairly uncomplicated way of simulating them.
I have a specific need to have around 120 text boxes on a particular user form and I really don't feel like having to put code in each one since they will be used virtually the same way.
Can anybody throw me a bone here?

Thanks.
Greg.

**Carim** · 02-12-2007, 11:58 AM

Hi,

Do you mean something like ...

Private Sub CommandOK_Click()
Dim i As Long
For i = 1 To 120
Sheets("Data").Range("A" & i) = Me.Controls("TextBox" & i).Value
Next i
Unload Me
End Sub

**gfrantsen1961** · 02-12-2007, 12:11 PM

Thanks. Great idea. I didn't realize you could use the controls collection like that.
Basically, I need to update a spreadsheet based on what is entered in a user-form. The user form is quite complex, basically a data entry form, with multiple tabbed pages, etc.
So, as long as I name the related text boxes with something simular and then a number (i.e. monthly1 - monthly10), I should be able to iterate through the correct boxes and update the appropriate spreadsheet row/column.
Sounds doable. Thanks.

However, there is no "update" button. Whatever the user enters it automatically sent to the sheet. What event would you use to trap this?