How to access a Collection member's properties

I have a Collection of Textboxes of a Class. I would like to iterate through the collection members and retrieve their Names, like MyColl(i).Name. Unfortunately, this attempt fails (property not avaialble). So I tried to assign the member to a new variable Tbx as MSForms.Textbox which is exactly the object type I added to the collection in the first place. However, Set Tbx = myColl(i) also fails (type mismatch). I would like to know the reason. Also, how can I get at the collection members?
awink
LVL 1
awinkAsked:
Who is Participating?
 
irudykConnect With a Mentor Commented:
You can try using the following code which will iterate through a user form (upon it being loaded) and display in a message box the name of each textbox control it finds.  You could then modify it to suite your needs.
Private Sub UserForm_Initialize()
Dim objControl As Control
For Each objControl In Controls
    If TypeName(objControl) = "TextBox" Then
        MsgBox objControl.Name
    End If
Next
End Sub

Open in new window

0
 
awinkAuthor Commented:
That's pretty far from the mark, I'm afraid. Please look at the attached code snippets. I want to access members of the Costs collection which are of Class CsDRsum which are derivatives of Textbox objects.
'  +++++++++ Public declarations +++++++++
    Public Costs As New Collection
          Public tbxSum As CsDRsum

' ++++++ Class Module +++++++
Option Explicit

Private WithEvents DRsum As MSForms.TextBox
Private EntryNum As Integer

Friend Property Get DRamount() As MSForms.TextBox
    Set DRamount = DRsum
End Property

Friend Property Set DRamount(NewTbx As MSForms.TextBox)
    Set DRsum = NewTbx
    EntryNum = Transfer.ExtractName(NewTbx.Name)(xSerial)
End Property
' +++++++ Standard module +++++++++
    Dim NewCtl As MSForms.Control
        With NewCtl

Set tbxSum = New CsDRsum
Set tbxSum.DRamount = NewCtl
Costs.Add Item:=tbxSum, Key:=NewCtl.Name

Open in new window

0
 
awinkAuthor Commented:
Actually, the answer to my question is very logical and relatively easy to see in the cool of morning:
Costs is a collection of Classes, not textboxes. To access any member of the collection an object of the class must be created:
Dim CsSumBox As CsDRsum
Set CsSumBox = Costs(i)
The Class has a single method, DRamount which contains the textbox object. To get at the textbox I need a textbox object to which I can then assign the Class's method:
Dim  SumBox As MSForms.TextBox
Set SumBox = CsSumBox.DRamount
Now all the textbox properties are accessible from the textbox object:
Debug.Print SumBox.Name
 
 
 
0
 
awinkAuthor Commented:
Thank you for trying.
0
 
irudykCommented:
Yep, I too would say my answer was way off the mark.  Thanks for sharing the correct solution, to which, I think, you should have accepted your comment as the solution (versus mine)...but thanks for the points/grade :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.