Loop through all objects in a frame

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
562
Hi,

I am curious if there is a way to loop through all control objects in a userform frame or do I have to loop through all objects in the userform and look for that particular frame as a parent? For instance, can I use a for each item in frame type loop?

My Current code draws a mistype error:

Code:
[COLOR=#333333]
Option Explicit


Public Sub Weekly_Button()


    HideAllSubFrames (Userform1.TestFrame)


End Sub




Public Sub HideAllSubFrames(ParentFrame As MSForms.FRAME)


    Dim ObjectList As Variant
    
    For Each ObjectList In ParentFrame.Controls
        If TypeName(ObjectList.Type) = "Frame" Then
            ObjectList.Visible = False
        End If
    Next
End Sub[/COLOR]
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Frame controls are like any other control that has a name, so assuming your frame control of interest is named Frame1, and further assuming you are clicking CommandButton1 on your UserForm to loop through the controls in Frame1, this will get you started to show how to do that with a message box that identifies each control's name that resides in Frame1.

Code:
Private Sub CommandButton1_Click()
Dim ctrl As Control
For Each ctrl In Frame1.Controls
MsgBox ctrl.Name
Next
End Sub
 
Upvote 0
Also, you might be interested to know that the reason you're getting a type mismatch error is that you've enclosed the argument for the calling procedure within parenthesis. You'll need to remove them.

Code:
Public Sub Weekly_Button()

    HideAllSubFrames UserForm1.TestFrame

End Sub

As you already know, the Call keyword is optional when calling a procedure. When specified, the argument list must be enclosed in parenthesis. However, if you omit them, as you've done in your case, you also have to omit the parenthesis from the argument list. Here's the reference for the Call statement...

https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/call-statement

So, in your case, while you've omitted the Call keyword, you've included the parenthesis around the argument. And, since the argument is an object, its default property is evaluated, which in this case is the Controls object. Hence the type mismatch error.

By the way, Type is not a property of the Control object. So you can just pass the object itself to TypeName...

Code:
If [COLOR=#333333]TypeName(ObjectList) = "Frame" Then[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top