Link to home
Start Free TrialLog in
Avatar of JohnSantaFe
JohnSantaFe

asked on

Looping through text boxes

I have a form with 26 text boxes.  I'm loading default values into the text boxes from a sheet. Instead of doing something like this:

frmMyForm.tbParam1 = Cells(1,1).Value
frmMyForm.tbParam2 = Cells(1,2).Value
....... up to 26

I'd like to setup a for loop to do the work
For x = 0 to 26
 frmMyForm.tbParamx = Cells(1,x).Value
Next x

The above code of course doesn't work but that's the idea.

Is there a way to cycle through the text boxes on a form instead of calling out each independently?

Thanks!
Avatar of Dave
Dave
Flag of Australia image
if your tbParam name is consistent that you could do this

Cheers

Dave
For Each ccont In frmMyForm.Controls
        If TypeName(ccont) = "TextBox" Then ccont.Value = ActiveSheet.Cells(1, CInt(Replace(ccont.Name, "tbParam", vbNullString)))
    Next ccont

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of MasterPage
MasterPage
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JohnSantaFe
JohnSantaFe

ASKER

Hi Dave,

What type is ccont in your example?

When I try it I get a run time type mismatch no matter if I declare ccont as an Object, Varient, or Text Box.

Thanks.
Avatar of JohnSantaFe
JohnSantaFe

ASKER

I ended up using Master Page's idea of an array of text boxes although in a simpler way that didn't involve creating a class