Loop a collection made by VBA class-CodePudding

I'm new to coding class modules, but pretty decent in normal modules... Now I'm trying to loop a collection made by me with no success. I can expand and see "the elements" inside VBE but get the error: "Object doesn't support this property or method".

In a standard module I have

Public collItems As Collection

The collection is populated inside a userform module on initialization

collItems.add cItems

cItems is an object made from New clsItems, which is the class module.

The class module consists of many userform controls like this:

Private WithEvents frm As msforms.Frame
Private WithEvents lbl As msforms.Label
Private WithEvents cmd As msforms.CommandButton
Private WithEvents txt1 As msforms.TextBox
Private WithEvents txt2 As msforms.TextBox

Not sure if Private is the way to go on this...

When the userform finish loading, a dynamic number of frames with all these textboxes inside each frame appears. It looks like a spreadsheet made from data inside MS Project. The command buttons job is to change a lot of the textbox's attributes/properties. So now I want to loop the collItems collection and write my code. But then I get an error...

I don't have any get or let in my class. Just a single set property. It might look stupid to add 10 unique textboxes inside the class, but that's how I made it work so far... All the form objects are given names that refer to row and column during creation and helps me identify them.

The failing code looks like this:

Sub changeBox(ByRef name As String)
For Each item in collItems.item(CLng(Replace(name, "cmd", "")))
     'blabla
Next item
End Sub

This test works and shows all the elements I want to loop but can't:

Set test = collItems.item(3) 'Meaning row 3 in userform

Any help on what I'm doing wrong or missing, in order to loop my specific textboxes and change their attributes would be much appreciated!

CodePudding user response：

So now I want to loop the collItems collection and write my code. But then I get an error...

The collection holds a sequence of objects of type cItems. Thus to loop the collection items, declare an object of this type to be used as the iterator.

Note, the class members must be publicly exposed in order to be accessible outside the cItems class. Not sure how you instantiate them, but they could be exposed through a public read-only property.

Public Property Get Form() As msforms.Frame
    Set Form = frm
End property

To loop:

Dim objCurrent As cItems, frm As msforms.Frame  

For Each objCurrent in collItems
     'here you can use the objCurrent to access the current item.
    Set frm = objCurrent.Form
    '...
Next objCurrent

To be able to get a control by name, create a dictionary in the cItems class to store the controls and use the key to retrieve the objects.

See an example below:

'dictionary
Private m_boxes As Object

Public Sub AddTextBox(ByVal Key As String, ByVal obj As msforms.TextBox)
    m_boxes.Add Key, obj
End Sub

Public Function GetTextBox(ByVal Key As String) As msforms.TextBox
    Set GetTextBox = m_boxes(Key)
End Function

Private Sub Class_Initialize()
    Set m_boxes = CreateObject("Scripting.Dictionary")
End Sub

Private Sub Class_Terminate()
    Set m_boxes = Nothing
End Sub

The to call it:

Dim txt As msforms.TextBox
Set txt = objCurrent.GetTextBox("txt1")