How to fill many texbox by using loop function in VBA

Question

Hi !

I made a user interface in VBA with many textbox. I read an excel sheet and I put all the value of this one in all the textbox of my user inteface. So the user can modify the values and then save it in the excel sheet.

Because we can't name the textbox like array (textBox(1), textbox(2)....) this is hard to fill the textbox by using a loop function.

I tried to use tag or tabindex property but I don't find the good way to proceed .... Is someone know an easy way to solve this !!!

Thanks

Answer 1

Iterate the form's control collection.

An example, say your user form is called myForm, then

myForm.Controls(i)

gets you a handle for any control in myForm. Now you can use control properties to identify which one you're looking at (label, textbox, button, etc). I'd suggest you use a Tag such as, hmmmm.... "TEXTBOX", to ease the process of id.

if myForm.Controls(i).Tag="TEXTBOX" then 'it's my textbox ! hurraay!!!

Answer 2

Yes you can.

Name your textboxes Textbox1, Textbox2 etc, then access them with

Form.Controls("Textbox" & ID)