Results 1 to 2 of 2

Thread: Multipage looping, looping through all the multipages on a userform

  1. #1

    Multipage looping, looping through all the multipages on a userform



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Sorry, clarification on the code sample, it does not save the user input to the cell, it reads the values in the cell and puts into the userform controls. Another area of the code does the saving to the cell.
    Thanks, Chris


    Quote Originally Posted by szchris384 View Post
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •