Hi,

I created 50 textboxes. Textbox1, Textbox2....Texbox50
Now i want to loop through them, but I don't know how to refer to them:

for i = 1 to 50

textbox?????

next i

Thanks,
Ken

Hi Ken

ActiveX Textboxes are not part of a textbox collection, so no, that is
impossible. A common workaround is to loop all controls and it this one
control is a textbox then do something. Or use those from the Forms toolbar.
Or:

Here's a really cool trick: You can create your own collections with VB /
VBA. You don't say where your boxes are, but if they're on a userform then
use this code for it:

Option Explicit ' top of module

Dim TBoxes As New Collection

Private Sub UserForm_Initialize()
TBoxes.Add Me.TextBox1
TBoxes.Add Me.TextBox2
TBoxes.Add Me.TextBox3
TBoxes.Add Me.TextBox4
'and so on
End Sub

'and our demo loop, here assigned to a button :

Private Sub CommandButton1_Click()
Dim L As Long
For L = 1 To TBoxes.Count
TBoxes(L).Text = "TBox " & L & " says Hello World"
Next
End Sub

'end of code

Collections are, in theory, memory hungry, but on a modern computer that
won't cause noticable problems. They are very flexible and blistering fast.

HTH. Best wishes Harald

"Ken Hsu" skrev i melding
...
Hi,

I created 50 textboxes. Textbox1, Textbox2....Texbox50
Now i want to loop through them, but I don't know how to refer to them:

for i = 1 to 50

textbox?????

next i

Thanks,
Ken

But they are part of the OLEObjects collection

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "TextBox" Then
MsgBox ActiveSheet.OLEObjects(i).Object.Text
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Harald Staff" wrote in message
...
Hi Ken

ActiveX Textboxes are not part of a textbox collection, so no, that is
impossible. A common workaround is to loop all controls and it this one
control is a textbox then do something. Or use those from the Forms
toolbar.
Or:

Here's a really cool trick: You can create your own collections with VB /
VBA. You don't say where your boxes are, but if they're on a userform then
use this code for it:

Option Explicit ' top of module

Dim TBoxes As New Collection

Private Sub UserForm_Initialize()
TBoxes.Add Me.TextBox1
TBoxes.Add Me.TextBox2
TBoxes.Add Me.TextBox3
TBoxes.Add Me.TextBox4
'and so on
End Sub

'and our demo loop, here assigned to a button :

Private Sub CommandButton1_Click()
Dim L As Long
For L = 1 To TBoxes.Count
TBoxes(L).Text = "TBox " & L & " says Hello World"
Next
End Sub

'end of code

Collections are, in theory, memory hungry, but on a modern computer that
won't cause noticable problems. They are very flexible and blistering
fast.

HTH. Best wishes Harald

"Ken Hsu" skrev i melding
...
Hi,

I created 50 textboxes. Textbox1, Textbox2....Texbox50
Now i want to loop through them, but I don't know how to refer to them:

for i = 1 to 50

textbox?????

next i

Thanks,
Ken

"medialint" skrev i melding
...
Hmmm ... impossible? It seemed to work for me!

No, you are addressing the control's names by constructing strings within a
loop, you are not not looping a collection. Which of course may work
perfectly, given that the controls keep their name & names given in the
desired tab order.

Yes you iterate through the Controls collection ...

No.Read the code again.

I didn't know it was rocket science!?

It's not. It's a really cool trick. Apologies for wasting bandwidth on
things like that.

Best wishes Harald