Syntax to read out dynamically named textbox in userform in vba excel?

Question

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.

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.

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?

    '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)

.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.

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.

*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.

try the code in my answer below

Shai Rado
– Shai Rado

2016-12-11 14:33:08 +00:00
Commented Dec 11, 2016 at 14:33 — Shai Rado
– Shai Rado, Commented Dec 11, 2016 at 14:33

Shai Rado · Accepted Answer · 2016-12-11 14:29:12Z

2

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.

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.

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

answered Dec 11, 2016 at 14:29

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Shai Rado Over a year ago

@ThomasInzina try running this code, it's working fine

user6432984 Over a year ago

I hope that you accept my humble apologies. Comment retracted.

Shai Rado Over a year ago

@ThomasInzina LOL , only if you +1 :)

user6432984 Over a year ago

LOL..I meant to do but got distracted.

Shai Rado Over a year ago

I was joking, B.T.W , congrats on the 10K !

|

score 1 · Accepted Answer · 2016-12-11 18:22:10Z

1

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]

edited Dec 11, 2016 at 18:22

answered Dec 11, 2016 at 16:25

user6432984

Collectives™ on Stack Overflow

Syntax to read out dynamically named textbox in userform in vba excel?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related