How to dynamically put cell values into a textbox on a multipage in excel?

Question

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.

MultiPage1.Pages(0).Controls.Copy is this the textbox that you are copying? — Siddharth Rout
No, that is the page that will be reproduced, then the new pages which has the same content as the reference page will have updates on what cell values are. — Jovanni G
I only have one text box in the page right now, I was just wondering what if I put another text box. Will it populate the second box with the same value as well? — Jovanni G
No, it will populate the 1st textbox that it finds and exit the loop as I am using Exit For When I work with Dynamic controls, I prefer not copying but recreating them from the scratch as that gives me more control over those objects. — Siddharth Rout

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-02-20T09:10:27

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.

enter image description here

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

enter image description here

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

enter image description here

HTH

How to dynamically put cell values into a textbox on a multipage in excel?

1 Answers