looping texboxes - possible or impossible?

K

Ken Hsu

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
[email protected]
 
H

Harald Staff

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
 
B

Bob Phillips

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)
 
H

Harald Staff

medialint said:
Hmmm ... impossible? It seemed to work for me!
Click to expand...

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 ...
Click to expand...

No.Read the code again.
I didn't know it was rocket science!?
Click to expand...

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

Best wishes Harald
 
Top