How to loop through userform frames with variable object name

Question

I am attempting to loop the a specific set of frames within each Multipage page in my VBA User_Form. However, it does not seem like I can use a variable object name with each frame control like I can with the pages.

I am getting an error

object doesn't support this property or method

at the following line

For Each cCont in Me.MultiPage1.Pages(PageName).Frames(DataFrame).Controls

My Code

Do While x <= Me.MultiPage1.Pages.Count
    PageName = "Page" & CStr(x)
    DataFrame = "DataFrame" & CStr(x)
    For Each cCont In Me.MultiPage1.Pages(PageName).Frames(DataFrame).Controls

Shai Rado · Accepted Answer · 2017-01-24 19:30:28Z

1

You actually can't iterate the way you would think you could.

First, you need to iterate through all Pages of your MultiPage1.

Second, loop through all Controls inside the current Page , and check if they are of type Frame, if they are you can iterate inside the Frame but the syntax is a little different (see in the code below).

Code

Option Explicit

Private Sub IterateIn_MultiPage()

Dim x As Long, j As Long
Dim cCont As Control

For x = 0 To Me.MultiPage1.Pages.Count - 1 ' <-- loop through all MultiPage Pages

    For Each cCont In Me.MultiPage1.Pages(x).Controls ' <-- loop through controls of current page
        If TypeOf cCont Is Frame Then ' <-- check if control type is Frame
            For j = 0 To cCont.Controls.Count - 1   '<-- loop through all items related to the current Frame collection
                MsgBox cCont.Controls(j).Name '<-- display a message box
            Next j
        End If
    Next cCont

Next x

End Sub

answered Jan 24, 2017 at 19:30

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Greg Over a year ago

This seems to be working to filter through each checkbox in the frame but adding If cCont.Controls.Value = True Then will not work to only find the ones which are checked

Shai Rado Over a year ago

This was not in your post, what are you trying to achieve ?

Greg · Accepted Answer · 2017-01-25 15:19:37Z

Thanks for the help on putting together the code @Shai, for anyone else wondering what the final code looks like here it is. This loops through each frame within each multipage and pastes the caption of checked checkboxes in my desired range.

Option Explicit

Sub IterateIn_MultiPage()
Dim x As Long, j As Long
Dim cCont As Control
Dim counter As Integer
Dim y As Integer
Dim Range As String
y = 1

For x = 0 To ImportData.MultiPage1.Pages.Count - 1
counter = 0
    For Each cCont In ImportData.MultiPage1.Pages(x).Controls
        Do While counter < 1
            If TypeOf cCont Is Frame Then
                For j = 0 To cCont.Controls.Count - 1
                    If cCont.Controls(j).Value = True Then
                            Range = "E" & y
                            If counter = 0 Then
                                Worksheets("Calculations").Range(Range) = cCont.Controls(j).Caption
                                counter = counter + 1
                            ElseIf counter = 1 Then
                                Worksheets("Calculations").Range(Range) = Worksheets("Calculations").Range(Range) & " & " & cCont.Controls(j).Caption
                                y = y + 1
                            End If
                    End If
                Next j
            End If
        Loop
    Next cCont

Next x

End Sub

Collectives™ on Stack Overflow

How to loop through userform frames with variable object name

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related