I have created a multipage with dynamic pages. When the userform is launched, the userform checks the values on a specific cell in a column if they are empty or not. then create a page for each of the non-empty cells.
Here is my code snippet
Private Sub UserForm_Initialize()
Dim i As Integer
Dim custDate As Date
Dim vID As String
'ActiveWorkbook.Worksheets("Sheet1").Activate
i = 0
custDate = DateValue(Now)
Range("A1").Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell.Value)
'MsgBox ActiveCell.Address
If custDate = ActiveCell.Value Then 'first column(A) are dates
MultiPage1.Pages.Add
MultiPage1.Pages(0).Controls.Copy 'page 1 is the reference page
i = i + 1 'row counter
ActiveCell.Offset(0, 2).Select 'go to column(C) on the same row where visit ids are located
vID = ActiveCell.Value
MultiPage1.Pages(i).Paste 'copy page 1 contents to new page for each row on the active worksheet
'I guess this is where you put the code to put values
'on a txtbox that was from the reference page which is page 1
ActiveCell.Offset(0, -2).Select 'go back to column(A) to check back dates
End If
ActiveCell.Offset(1, 0).Select 'move to the next row
Loop
MultiPage1.Value = i 'select the new page on the userform
End Sub
Now my problem is how to put the values from a cell to a textbox that was copied from the reference hidden page to the dynamically created new page. I just started programming VBA last night. I am an android applications developer, so it's kind of hard to adjust as of this moment.
I think this is what you are trying?
After you paste the control, try this
'
'~~> Rest of your code
'
MultiPage1.Pages(i).Paste
For Each ctl In Me.MultiPage1.Pages(i).Controls
If TypeOf ctl Is MSForms.TextBox Then
'~~> Your code here
ctl.Text = vID
Exit For
End If
Next
'
'~~> Rest of your code
'
Also declare this as the top of your code
Dim ctl As Control
FOLLOWUP (From Comments)
If you have multiple Controls
of the same type then I prefer not copying and pasting but recreating them from scratch. This gives me more control over those Controls
However, if you still want to use the Copy - Paste method then use the .Tag
property. See this example
Create a userform as shown in the snapshot below.
In Page(0) set tags for each textbox.
Let's use this code in the userform
Option Explicit
Dim ctl As Control
Private Sub CommandButton1_Click()
Debug.Print "Page (0):-"
For Each ctl In Me.MultiPage1.Pages(0).Controls
If TypeOf ctl Is MSForms.TextBox Then
Debug.Print ctl.Name; "==="; ctl.Tag
End If
Next
Debug.Print "---"
Debug.Print "Page (1):-"
MultiPage1.Pages(0).Controls.Copy
MultiPage1.Pages.Add
MultiPage1.Pages(1).Paste
For Each ctl In Me.MultiPage1.Pages(1).Controls
If TypeOf ctl Is MSForms.TextBox Then
Debug.Print ctl.Name; "==="; ctl.Tag
End If
Next
Debug.Print "---"
Debug.Print "Page (2):-"
MultiPage1.Pages.Add
MultiPage1.Pages(2).Paste
For Each ctl In Me.MultiPage1.Pages(2).Controls
If TypeOf ctl Is MSForms.TextBox Then
Debug.Print ctl.Name; "==="; ctl.Tag
End If
Next
End Sub
When you run the code, you will see this output in the screen
If you notice that the .Tag
doesn't change. So we can effectively use this if we have more controls. See this example
Option Explicit
Dim ctl As Control
Private Sub CommandButton1_Click()
MultiPage1.Pages(0).Controls.Copy
MultiPage1.Pages.Add
MultiPage1.Pages(1).Paste
For Each ctl In Me.MultiPage1.Pages(1).Controls
If TypeOf ctl Is MSForms.TextBox Then
Select Case ctl.Tag
Case "A"
'~~> Your code goes here to put text in this textbox
ctl.Text = "AAAA"
Case "B"
'~~> Your code goes here to put text in this textbox
ctl.Text = "BBBB"
End Select
End If
Next
End Sub
When you run it, you get
HTH