Loop a formula to populate multiple TextBoxes on UserForm

[Lectricman]

Can anyone help with what I thought would be an easy job?
I would like to populate 13 TextBoxes (101 to 113) with data held in a range of cells.
The following formula does the trick on a single box, but I'm going to end up with maybe 100 or more boxes to do.

TextBox101.Text = Application.WorksheetFunction.Text(ThisWorkbook.Sheets("Calcs").Range("B15").Value, "[h]:mm")

The data is held in a single row: (B15:N15).
Many thanks for any help.

 

[Fluff]

TRy

    For i = 1 To 13
    Controls("TextBox10" & i).Text = Application.WorksheetFunction.Text(ThisWorkbook.Sheets("Calcs").Cells(15, i + 1).Value, "[h]:mm")
    Next i

 

[Rick Rothstein]

TRy

    For i = 1 To 13
    Controls("TextBox10" & i).Text = Application.WorksheetFunction.Text(ThisWorkbook.Sheets("Calcs").Cells(15, i + 1).Value, "[h]:mm")
    Next i

I think you need to change your code as follows (your concatenations will have a problem when "i" reaches 10)...

[table="width: 500"]
[tr]
	[td]For i = 101 To 113
  Controls("TextBox" & i).Text = Application.WorksheetFunction.Text(ThisWorkbook.Sheets("Calcs").Cells(15, i + 1).Value, "[h]:mm")
Next i[/td]
[/tr]
[/table]

 

[Fluff]

Good point Rick.
Serves me right for testing on only 3 txtboxes & not thinking!

For i = 101 To 113
  Controls("TextBox" & i).Text = Application.WorksheetFunction.Text(ThisWorkbook.Sheets("Calcs").Cells(15, i -99).Value, "[h]:mm")
Next i

Although the Cells needs to be changed as well

 

[Lectricman]

Brilliant.
Thank you very much.

 

[Rick Rothstein]

Good point Rick.
Serves me right for testing on only 3 txtboxes & not thinking!

For i = 101 To 113
  Controls("TextBox" & i).Text = Application.WorksheetFunction.Text(ThisWorkbook.Sheets("Calcs").Cells(15, i -99).Value, "[h]:mm")
Next i

Although the Cells needs to be changed as well

Good point Fluff.
Serves me right for not looking at the entire code line.

 

[Fluff]

Brilliant.
Thank you very much.

Glad we could help & thanks for the feedback