PDA

View Full Version : For loop to fill textfields



bvs123
08-06-2011, 04:17 AM
Hi,

I have two userforms, let's say UserForm1 and UserForms2.

When pressing a button on UserForm2, 12 txtFields on UserForm1 should be filled.

It works if I would create 12 lines like:

UserForm1.txtField1.Value = Range1.Offset(0, 20)
UserForm1.txtField2.Value = Range1.Offset(0, 21)
etc.


But I would like to create this via a for loop, I came up with the thing below but it does not work.


Dim k As Long
For k = 1 To 12
UserForm1.txtField & k & .Value = Range1.Offset(0, k + 19)
Next k


Please help me. Thanks in advance!

GTO
08-06-2011, 04:50 AM
Hi there :-)

Regardless of where the vals are coming from, the problem is in referencing the control (in this case, textboxes) that we are wanting to access. I may not get this technically correct, but in gist, 'txtField1' is the Object (or CodeName) name. Think of it this way - if your changed the CodeName of "Sheet1" to "shtSheet1", this would not work:

"shtSheet" & 1 & .Value = "23 skidoo!"

Fortunately, the userform has a Controls Property, so we can use a string thusly:

Private Sub CommandButton1_Click()
Dim i As Long

For i = 1 To 12
Me.Controls("TextBox" & i).Value = i
Next
End Sub

Hoep that helps,

Mark

mikerickson
08-06-2011, 08:45 AM
Another technique, if you are using descriptive control names, is to loop through an array of controls


Dim oneControl As Variant

For each oneControl in Array(txtName, txtPhone, txtAddress, txtCity)