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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

irudykCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.