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, Feb 20 '13 at 08:59
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, Feb 20 '13 at 09:52
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, Feb 20 '13 at 10:02
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, Feb 20 '13 at 10:07
Ok. I get it. That is what I had in mind as well, I am just confused right now, android scripts are mixing up on my mind. hehe.. But thanks a lot man. You rock. — Jovanni G, Feb 20 '13 at 10:09
The easiest way to handle such multiple textboxes is using the Tag property. In Page(0) set tags for each textbox. and when you copy the textbox the names will change but the tags will remain same which can be used to identify them. let me know if you want an example? — Siddharth Rout, Feb 20 '13 at 10:10
ok gimme 15 mins and I will update the post with screenshots :) — Siddharth Rout, Feb 20 '13 at 10:12

Siddharth Rout · Accepted Answer · 2013-02-20T10:29:50.973

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

Thank you for the quick reply, I will go ahead and check out his code right now. — Jovanni G, Feb 20 '13 at 09:52
OMG... it worked just as I wanted it to be. Thanks a lot man. but what if I have 2 text boxes on a page? how would I know which one will be copied on the appropriate textbox? — Jovanni G, Feb 20 '13 at 09:58
Thank you so much Siddharth. Now I am finally on the right track. Your instructions were very clear dude. You rock. — Jovanni G, Feb 20 '13 at 10:34

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

1 Answers1