How to iterate through all Text Boxes on a Form?

[Ed]

I can iterate through all the controls on a UserForm, but I can't find a
"Type" or other property that will let me get just the Text Boxes. Any
help?

Ed

 

[Harald Staff]

Hi Ed

Put them into your own new custom collection like this:

Option Explicit

Dim MyTextBoxes As Collection

Private Sub UserForm_Initialize()
Set MyTextBoxes = New Collection
MyTextBoxes.Add Me.TextBox1, "T1"
MyTextBoxes.Add Me.TextBox2, "T2"
MyTextBoxes.Add Me.TextBox3, "T3"
End Sub

Private Sub CommandButton1_Click()
Dim TB As MSForms.TextBox
For Each TB In MyTextBoxes
MsgBox TB.Text
Next
MsgBox MyTextBoxes("T2").Text
MsgBox MyTextBoxes(1).Text
End Sub

HTH. Best wishes Harald

 

[Tom Ogilvy]

for each ctrl in Userform1.controls
if typeof ctrl is MSForms.Textbox then
ctrl.Value = ""
end if
Next

 

[Ed]

Wow! You've helped quite a bit today alone, Tom. Hope some day I can
return the favor.

Thank you!
Ed

 

[Harald Staff]

Hi Tom

This is probably only a theoretical problem, but code that loops all
controls might loop and test 200+ of them every time just to get something
done to 5 of them.

Best wishes Harald

 

[Ed]

Harald - I didn't think of that. At the moment, I don't have that problem.
Later on, though (assuming I get good enough that people want more
complicated stuff), that can become a problem. I will look into the custom
collections - that has definite possibilities.

Thanks.
Ed