szchris384
New member
- Joined
- Mar 2, 2014
- Messages
- 8
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 16.0.12827.20235 64bit
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:
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