Calling Textboxes created in runtime with a form how to reference in a loop

I am creating a userform where by which several text boxes (up to 60) are created in runtime based on the number of periods that the user enters.

i used this code to create the text boxes:
Set txtCntrl = .Add("Forms.TextBox.1", "txtDemand" & txtT, True) 'in this case txtT is incrementing up to the number of periods
and so in this case i end up with several textboxes (txtDemand1, txtDemand2, .....)

when the user clicks OK i want to take all the values from the form and put them into a sheet.
i have found that the following line works fine (in this example taking the value in txtDemand1 and putting it into cell "A46"):
Range("A46") = frmAggPlan!txtdemand1.Value

The problem is that i want to automate this some how using a variable and concatenating it. but i dont it seems that the i can not do this in the same way that i created the boxes. For example i would like to set up a looop on user press ok like this.

Dim i As Integer
For i = 1 to txtT
Range ("A46").offset(i-1) = frmAggPlan!txtDemand & i 'i'm not able to concatenate like this here, so looking for a way to have all the runtime created text boxes to output to my sheet

Next i

Any help would be welcome.

first of all: do not complicate your code by creating & formatting controls at runtime
Create the 60 textboxes in design mode and make them invisible.
Put all the code that pertains to the userform in de codemodule of this userform !

At runtime you can use, dependent of anything (but also some user input), this code to make the textboxes visible

Code:

---

Sub M_snb()
  for j=1 to val(inputbox("number 1 to 60"))
      me("TextBox" & j).visible=true
  next
End Sub

---

To write the result into a worksheet:

Code:

---

Sub M_snb()
  redim sn(59,0)

  for j=0 to 59
    if me("Textbox" & j+1)=visible then sn(j,0)=me("Textbox" & j+1)
  next

  thisworkbook.sheets(1).cells(1,1).resize(60)=sn
End Sub

---

Reduce reading/writing from/into a worksheet to the minimum for speed's sake.

Quote:

---

Originally Posted by CABBA

i want to automate this some how using a variable and concatenating it. but i dont it seems that the i can not do this in the same way that i created the boxes.

---

Something along the lines of:

Code:

---

For i = 1 To 5
  Range("A46").Offset(i - 1) = Me.Controls("txtDemand" & i)
Next i

---

?

Quote:

---

Originally Posted by p45cal

Something along the lines of:

Code:

---

For i = 1 To 5
  Range("A46").Offset(i - 1) = Me.Controls("txtDemand" & i)
Next i

---

?

---

Thanks this worked for me.

But it doesn't comply to:

Quote:

---

Reduce reading/writing from/into a worksheet to the minimum for speed's sake.

---

So why do you ignore http://www.vbaexpress.com/forum/show...l=1#post374083 ?

Maybe because it didn't work?
If Me("Textbox" & j + 1).Visible Then…
might help.

Yes that would be very helpful.. ;)