Multipage looping, looping through all the multipages on a userform

szchris384 said:

Hello, I'm trying to loop through all the multipages in my userform, then loop through all the pages on each multipage, then save all the info in the input controls on each page.
I have 5 frames on the userform
3 frames have a multipage control
Each multipage has 5-7 pages
Each page has 3-20 textbox, combobox, checkboxes.

I'm trying to capture all the data that's entered into all the pages in all 3 of the multipages in a specific order based on the tab order of each page in the multipage

I've managed to do loop each page within each multipage, but can't figure out how to loop through all the multipages automatically
I've hard coded the 3 loops for the 3 different mulitpages, but that's just silly, there must be a way to loop through automatically,

Processing must be done in multipage order of 1-3, ie MultiPage1, MultiPage2 and MultiPage3 in that order.
-the Multipages were not added to the userform in that order.
-so when i loop through the Controls on the userform, the order of the MP's show up as 3,1,2, which was the order they were added.

Thanks in advance for any input !
Chris


Here is what i have so far:

Code:

    'finds total number of multipage controls
    'Dim intMPTotal As Integer
    'Dim myMP As Control
    'For Each myMP In Me.Controls
    '    If TypeName(myMP) = "MultiPage" Then
    '        intMPTotal = intMPTotal + 1
    '        'Debug.Print myMP.Name
    '    End If
    'Next


    Dim ws As Worksheet
    Set ws = Worksheets("MVI_Info")


    Dim intColumn As Integer   'column number of worksheet
    Dim Ctrl As Control
    Dim Pg As Object


    Dim iMaxInputs As Integer
    Dim iTabIndex As Integer
    
    'FIRST MULTIPAGE
    For Each Pg In MultiPage1.Pages 'hard coded multipage name
        
        'loop to find max input fields
        iMaxInputs = 0
        For Each Ctrl In Pg.Controls
            If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "CheckBox" Or TypeName(Ctrl) = "ComboBox" Then
                iMaxInputs = iMaxInputs + 1
            End If
        Next
        
        'Loops through each control, if it's tabindex matches the current tab counter, save data to cell
        For iTabIndex = 1 To iMaxInputs
            For Each Ctrl In Pg.Controls
                If Ctrl.TabIndex = iTabIndex Then
                    intColumn = intColumn + 1
                    'saves control name to 1st row in worksheet
                    ws.Cells(1, intColumn).Value = Ctrl.Name
                    'saves value to cell in worksheet in 2nd row
                    If TypeName(Ctrl) <> "CheckBox" Then
                        Ctrl.Text = ws.Cells(2, intColumn).Value
                    Else    'textbox or combobox
                        Ctrl.Value = ws.Cells(2, intColumn).Value
                    End If
                End If
            Next    'Ctrl
        Next iTabIndex
    Next    'Pg
    
    'SECOND MULTIPAGE
    For Each Pg In MultiPage2.Pages 'hard coded multipage name
        
        'loop to find max user input fields
        iMaxInputs = 0
        For Each Ctrl In Pg.Controls
            If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "CheckBox" Or TypeName(Ctrl) = "ComboBox" Then
                iMaxInputs = iMaxInputs + 1
            End If
        Next
        
        'Loops through each control, if it's tabindex matches the current tab counter, save data to cell
        For iTabIndex = 1 To iMaxInputs
            For Each Ctrl In Pg.Controls
                If Ctrl.TabIndex = iTabIndex Then
                    intColumn = intColumn + 1
                    'saves control name to 1st row in worksheet
                    ws.Cells(1, intColumn).Value = Ctrl.Name
                    'saves value to cell in worksheet in 2nd row
                    If TypeName(Ctrl) <> "CheckBox" Then
                        Ctrl.Text = ws.Cells(2, intColumn).Value
                    Else    'textbox or combobox
                        Ctrl.Value = ws.Cells(2, intColumn).Value
                    End If
                End If
            Next    'Ctrl
        Next iTabIndex
    Next    'Pg
    
    'THIRD MULTIPAGE
    For Each Pg In MultiPage3.Pages 'hard coded multipage name
        
        'loop to find max user input fields
        iMaxInputs = 0
        For Each Ctrl In Pg.Controls
            If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "CheckBox" Or TypeName(Ctrl) = "ComboBox" Then
                iMaxInputs = iMaxInputs + 1
            End If
        Next
        
       'Loops through each control, if it's tabindex matches the current tab counter, save data to cell
       For iTabIndex = 1 To iMaxInputs
            For Each Ctrl In Pg.Controls
                If Ctrl.TabIndex = iTabIndex Then
                    intColumn = intColumn + 1
                    'saves control name to 1st row in worksheet
                    ws.Cells(1, intColumn).Value = Ctrl.Name
                    'saves value to cell in worksheet in 2nd row
                    If TypeName(Ctrl) <> "CheckBox" Then
                        Ctrl.Text = ws.Cells(2, intColumn).Value
                    Else    'textbox or combobox
                        Ctrl.Value = ws.Cells(2, intColumn).Value
                    End If
                End If
            Next    'Ctrl
        Next iTabIndex
    Next    'Pg

Click to expand...