在vba excel中读取userform中动态命名的文本框的语法？

I dynamically create a userform named UserForm1. In it, I generate textboxes, which will be filled manually by a user. Afterwards I would like to read their value but I don't know how to call the (value of the) textbox.

我动态创建一个名为UserForm1的用户窗体。在其中,我生成文本框,将由用户手动填充。之后我想读他们的价值,但我不知道如何调用(文本框的值)。

The following bit of code is used to create and name the textboxes:

以下代码用于创建和命名文本框:

With UserForm1 'scaling userform
    .Height = max_width
    .Width = 600
End With

For test1 = 1 To nr_of_zeros + 1 'create textboxes
    Set ctextbox = Controls.Add("forms.textbox.1", test1) 'creating textbox
    With ctextbox 'scaling textbox
        .Height = 20
        .Width = 40
        .Top = 40 + 25 * test1
        .Left = 400
    End With

So the textbox will have the name of the number (integer or long?) of test1.

因此,文本框将具有test1的数字(整数或长?)的名称。

I tried the following sentences to try to read the value of the textbox into: absorb_text but unsuccesfull so far. Does anybody know the correct complete way to call the above created textbox?

我尝试了下面的句子来尝试将文本框的值读入:absorb_text但到目前为止unsuccesfull。有人知道调用上面创建的文本框的正确完整方法吗?

    'ctextbox.name = Controls.Add("forms.textbox.1", test1) 'creating textbox
    'absorb_text = forms("textbox").Controls(test1).Value

    'absorb_text = forms.("UserForm1").textbox.(test1).value
    forms.textbox.1.(test1)
    strname = TextBox1(test1).Text

(Analog to, one does not call cell "A2" by)

(模拟,一个不称为单元格“A2”)

.range("A2")

but with

Thisworkbook.worksheets("sheetname").range("A2").text
Thisworkbook.worksheets("sheetname").range("A2").value
Thisworkbook.worksheets("sheetname").cells(2,1).text
Thisworkbook.worksheets("sheetname").cells(2,1).value

Thank you very much! I was still wondering why/what the 1 does in "forms.textbox.1" I copied it because it worked, but am confused by its function.

非常感谢你!我仍然想知道为什么/ 1在“forms.textbox.1”中做了什么我复制它因为它起作用,但我的功能感到困惑。

Also in light of your discussion below: I believe, technically the code does not look for a control name that is equal to the/a number 1 but to a string character which happens to equal the character 1. hence it is not equal to a number but a character.

另外根据你下面的讨论:我相信,从技术上讲,代码不会查找等于/ 1的控制名称,而是查找恰好等于字符1的字符串字符。因此它不等于a数字,但一个字符。

*argument against that is that it still works with: `If ctrl.Name = 1 Then' in which case I would think the 1 is treated as a number.

*反对的论点是它仍然适用于:`如果ctrl.Name = 1那么'在这种情况下我会认为1被视为一个数字。

2 个解决方案

#1

When you created your TextBoxes in your code line: Set ctextbox = Controls.Add("forms.textbox.1", test1) the names of your Textboxes is 1, 2, 3, etc.

在代码行中创建TextBox时:设置ctextbox = Controls.Add(“forms.textbox.1”,test1),文本框的名称为1,2,3等。

In order to read your TextBoxes (created at run-time) I loop through all Controls of the User_Form, check if it's type TextBox, and check the Name property of the Control.

为了读取TextBox(在运行时创建),我遍历User_Form的所有控件,检查它是否为TextBox类型,并检查Control的Name属性。

Code

Option Explicit

Private Sub ReadRunTimeTextBox()

Dim ctrl            As Control
Dim absorb_text     As String

' loop through all control in user form
For Each ctrl In Me.Controls
    ' check if control is type TextBox
    If TypeName(ctrl) = "TextBox" Then
        ' if control name is 1 (first created TextBox in your array)
        If ctrl.Name = "1" Then
            absorb_text = ctrl.Text

            ' the message box is for debug only
            MsgBox absorb_text
        End If
    End If
Next ctrl

End Sub

#2

Here are two more ways to refer to the dynamically added control

以下是另外两种引用动态添加控件的方法

Const nr_of_zeros  = 4
For test1 = 1 To nr_of_zeros + 1 'create textboxes
    Debug.Print Controls(test1)
Next

Debug.Print Me![1]
Debug.Print Me![2]
Debug.Print Me![3]
Debug.Print Me![4]

#1