Solved: Making a Collection of some Form Controls

[SamT]

I've been trying to use a Collection of Frames (controls) to iterate just thru the Frames on the form instead of all the controls.

The "Collection_Of_Frames" Function does return a collection with the correct count. If I "Collect" Names I can access them as Strings, but when I try to get the Frames, I don't know what I am getting because I don't know what I'm doing .

If I get this to work I'll add Vars so it can be called for any Form.
i.e. Collection_Of_frames(MyForm_Var As UserForm, MyNamePart_Var As String) as Collection. This is just so I can figger things out without running my entire form and all its initializing code.

[vba]
Function Collection_Of_Frames() As Collection
'assumes UserForm1 Contains 1 or more Frames

Dim MyCtrl As Control
Dim MyFrames As New Collection

For Each MyCtrl In UserForm1.Controls
If MyCtrl.Name Like "Frame*" Then
MyFrames.Add (MyCtrl)
End If
Next MyCtrl


Set Collection_Of_Frames = MyFrames

End Function

[/vba]

But I can't seem to access any of the "whatevers" in the collection. In the following, I have commented out the lines that don't work with the error msgs at the ends of the lines.

[vba]
Sub Test()
Dim MyName As String
Dim MyCollection As New Collection
Dim MyFrame As Frame
Dim MyCheck As Boolean
Dim MyString As String
Dim MyObject As Object
Dim MyCtrl As Control
Dim MyCount As Long

Set MyCollection = Collection_Of_Frames
MyCount = MyCollection.Count 'Count is right

Set MyObject = MyCollection.Item(1)
MyCheck = MyObject Is MyCollection(1) 'MyCheck = true

'Set MyFrame = MyCollection.Item(1) ' Error = Type Mismatch
'Set MyCtrl = MyCollection(1) 'Error = Type Mismatch
'MyString = MyCollection.Item(1) ' Error = Invalid Property Assignment
'MyName = MyCollection.Item(1).Name 'Error = Object doesn't support this Property or Method.
'Set MyCollection(1).BackColor = 14794198 'Error = Object doesn't support this Property or Method.
'Set MyCollection(1).Caption = "This is a Frame" 'Error = Object doesn't support this Property or Method.

End Sub
[/vba]

I did use something like
[vba]
For each MyCtrl in UserForm.controls
If MyCtrl.Name Like *Frame* Then
Set MyFrame = MyCtrl
End If
For Each MyCtrl in MyFrame.Controls
Do Something
next
next
[/vba]
but I have to do this in several procedures and it just seems more elegant to use a function.

Please help
SamT

[Bob Phillips]

Is thgis any good for you?

First create a clas called clsUserFormEvents with this code

[vba]

Public WithEvents mFrameGroup As msforms.Frame
[/vba]

Set up the class like this

[vba]

Dim cFrameEvents As clsUserFormEvents
Dim ctl As msforms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "Frame" Then
Set cFrameEvents = New clsUserFormEvents
Set cFrameEvents.mFrameGroup = ctl
mcolEvents.Add cFrameEvents
End If
Next
Set cFrameEvents = Nothing
[/vba]

and access like so

[vba]

MsgBox mcolEvents.Item(1).mFrameGroup.Name
MsgBox mcolEvents.Item(2).mFrameGroup.Name
[/vba]

[rory]

SamT,
Your function will work if you use:
[VBA]Function Collection_Of_Frames() As Collection
'assumes UserForm1 Contains 1 or more Frames

Dim MyCtrl As msforms.Control
Dim MyFrames As New Collection

For Each MyCtrl In Me.Controls
If TypeName(MyCtrl) = "Frame" Then
MyFrames.Add MyCtrl, MyCtrl.Name
End If
Next MyCtrl


Set Collection_Of_Frames = MyFrames

End Function[/VBA]

Your problem was the parentheses around MyCtrl when you added the frame to the collection - they dereference the control so you end up adding its name instead.

[SamT]

Thanks guys,

I'll give these a try and let you know how it comes out.

Probably be a couple of hours before I can work on it, though.

SamT

[SamT]

XLD, I went with Rory's solution, because, well...frankly I'm too green to understand what you did.

Rory, Thanks and thanks for the insight on TypeName and the use of Object as its parameter. Yes, I will remember to try ()'s when nothing seems to work with Objects.

What you showed me inspired me to expand the function's abilities and now it will collect by control type and/or name segment.

I have attached the results, but as you can see, I can't figure out how to test for the presence of an actual UserForm in the first function parameter.

If someone thinks it's worth it and wants to error proof it, I give permission to distribute it for free thru VBAExpress.com.

Again, thanks to both a ya!

SamT

Ps: I can't recall if I should have edited the "How To" comments. Oh well...