vba 如何在excel的多页上动态地将单元格值放入文本框中？

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

提问by Jovanni G

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.

现在我的问题是如何将单元格中的值放入从引用隐藏页面复制到动态创建的新页面的文本框。我昨晚刚开始编程 VBA。我是一名 android 应用程序开发人员，所以目前很难调整。

采纳答案by Siddharth Rout

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 Controlsof the same type then I prefer not copying and pasting but recreating them from scratch. This gives me more control over those Controls

如果您有多个Controls相同类型，那么我不喜欢复制和粘贴，而是从头开始重新创建它们。这让我可以更好地控制那些Controls

However, if you still want to use the Copy - Paste method then use the .Tagproperty. See this example

但是，如果您仍想使用 Copy - Paste 方法，请使用该.Tag属性。看这个例子

Create a userform as shown in the snapshot below.

创建一个用户表单，如下面的快照所示。

In Page(0) set tags for each textbox.

在 Page(0) 中为每个文本框设置标签。

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 .Tagdoesn't change. So we can effectively use this if we have more controls. See this example

如果你注意到.Tag没有改变。所以如果我们有更多的控制，我们可以有效地使用它。看这个例子

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

HTH