Loop through a collection of controls

[Roldy]

I have a userform with a bunch of text boxes which I added to a collection using the text boxes name as the index string. This code is in the initialize event of the userform. The user enters data and clicks on a button. This button runs a sub to gather the data in each text box. Since I already have my collection of text boxes, I don't need to rebuild it...I just need to get the control so I can get the value that is in it. What is the correct way of looping through the collection to get the control? In the form initialization, I am building the collection using a custom class. I'm doing it this way because later down the road I'm going to use this class to control some text box events.



Private Sub UserForm_Initialize()


Set colTbxs = New Collection

For Each nControl In Me.Controls

Select Case TypeName(nControl)

Case "TextBox"

Set clsObject = New clsObjectHandler
Set clsObject.Control = nControl
colTbxs.Add clsObject, nControl.Name

Case Else

End Select

Next nControl

End Sub


In the button click event I do For Each loop to try and access each item in the collection. This is where I am having problems. As a test, I'm trying to display the name of the control but can't figure out how to access any of the properties. The image shows that it has a class instance of my custom class. I suspect I need to use the Set command.


Dim nCol as Variant

For Each nCol In colTbxs
????


Next nCol



25915

[Leith Ross]

Hello Roldy,

Since you are Sub-Classing the TextBoxes, add a Parent property to you new class that returns the original TextBox object. This will make your code a lot easier to work with.

[Paul_Hossler]

Not sure I understand the big picture, but I usually do something like this

Standard Module:



Option Explicit


Public colTbxs As Collection


Sub test()
Dim O As clsTextBox

Set colTbxs = New Collection

Load UserForm1

For Each O In colTbxs
MsgBox O.Ctrl.Name
Next


End Sub





Class Module:



Option Explicit


Private m_Control As Control


Property Let Ctrl(c As Control)
Set m_Control = c
End Property


Property Get Ctrl() As Control
Set Ctrl = m_Control
End Property





UserForm:



Option Explicit


Private Sub UserForm_Initialize()
Dim c As Control
Dim oTB As clsTextBox
For Each c In Me.Controls
If TypeName(c) = "TextBox" Then
Set oTB = New clsTextBox
oTB.Ctrl = c
colTbxs.Add oTB, c.Name
End If
Next
End Sub

[SamT]

'Module level variable
Dim colTbxs as Collection


For Each nControl In Me.Controls
If TypeName(nControl) = "TextBox" Then
'Code to filter unwanted textboxes
colTbxs.Add nControl
End If
Next



For Each nCol In colTbxs
X = nCol.Name
Y = nCol.Tag
Z = nCol.Text
Next

[Roldy]

Eventually I'm going to be including event procedures in my class to handle events for all my text boxes. That is why I'm building my collection with my custom class.

Edit: This actually might be what I'm looking for. I just need to adapt it so that my collection is updated with values in the text box after a button is pressed.

[Roldy]

My collection is going to be built from my custom class. Check out the response above.

[Roldy]

Something like this?


Set clsObject = New clsObjectHandler
Set clsObject.Control = nControl

With clsObject
.parentObj = nControl
End With

colTbxs.Add clsObject, nControl.Name

And my class would be...


Private Sub Class_Initialize()

Set pObj = Nothing

End Sub


Property Get parentObj() As Object

Set parentObj = pObj

End Property


Property Let parentObj(value As Object)

Set pObj = value.Parent

End Property