How to fill many texbox by using loop function in VBA

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 t开发者_高级运维ried 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

Yes you can.

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

Form.Controls("Textbox" & ID)

you can use this code to fill multi textboxs

    Dim rs As Object
    Dim i As Integer
    Dim ctlr As Control
     Set rs = Me.Recordset.Clone
     For Each ctlr In Me.Controls
     If TypeOf ctlr Is TextBox Then
      For i = 0 To ctlr.Controls.Count
      On Error Resume Next
       ctlr.Value = rs!SomeField 
       rs.MoveNext
      Next i
    End If

Next

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!!!