Collections in VBA

[slwolf8]

I want to control the visibility of certain frames on a form (Word XP). I would like to create a collection of the affected frames. I know I have to create a variable and declare it as a collection and that I use the add method to add things to the collection. How can I add the frames to the collection and how can I have my code cycle through each frame? This is what I tried so far:

Dim frColl As Collection
Dim frCtrl As Frame

frColl.Add frCR
frColl.Add frForPat
frColl.Add frForTM
frColl.Add frLit
frColl.Add frMisc
frColl.Add frPCT
frColl.Add frUSPat
frColl.Add frUSTM


For Each frCtrl In frColl
frCtrl.Visible = True
Next

Thanks for any help you can provide.

 

[Tranman]

You might want to add a key to each item so you can refer to it by name, instead of by ordinal position in the collection:

Dim frColl As Collection
Dim frCtrl As Frame

frColl.Add frCR,frCR.Name
frColl.Add frForPat,frForPat.Name
frColl.Add frForTM,frForTM.Name etc
frColl.Add frLit
frColl.Add frMisc
frColl.Add frPCT
frColl.Add frUSPat
frColl.Add frUSTM


For Each frCtrl In frColl
frCtrl.Visible = True
Next

Aside from that, and the fact that a lot of times, people use variants instead of a specific object type to refer to the members of their collections, your logic looks fine to me.

Seems like a neat idea.

You know about the controls collection of your form, right?

Tranman

 

[JVerdaasdonk]

Hi Slwolf8,

I don't now wat you meen by making a group off controls and then control the group by making them visible (or not)

This is how I control a group off Frames:

Dim ctrl As Control

Private Sub CommandButton1_Click()
For Each ctrl In Me.Controls
If Left(ctrl.Name, 3) = "Fra" Then
ctrl.Visible = False
End If
Next ctrl

End Sub

I just make sure that the ctrl's I wan't to group (as you call it) have the same 3 letters at the begining of the Objectname.

It's just an other way at looking at this question.

Is this a possibillity for you?

Enjoy,
Joost Verdaasdonk

 

[slwolf8]

Thanks to both of you for replying! Joost, your solution will work but I was hoping to use a method more like the one I posted. I am getting an error "Method or data member not found" on my line "frCtrl.visible = true". I want to do it like this so that I can stick with the naming convention I have used thus far in my forms and also because I believe this is how they do it in .Net so I figured it would be good practice . Any ideas?

 

[TonyJollans]

Hi slwolf8,

Visible is not a Property of a Frame. Try declaring frCtrl as a [blue]Control[/blue] or an Object.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[slwolf8]

Oh. Visible is a property of frames in design time so I just assumed I would be able to access it run time.