Name property of dynamically created textboxes

Question

First a little background information:

I am working on a project in Excel. When you open the file, a userform pops up and asks for input in multiple textboxes. It then puts the input in certain cells in the Excel document. Then another userform pops up, and asks for more input, which is inserted in cells as before. Next, it requests numerical input in textboxes, and this data inserts the respective number of rows under each category in the Excel document.

Now for the problem.

The next thing I want to do is have a userform which dynamically creates a certain number of labels and textboxes based on the numbers inputted by the user in the previous userform. I have already figured out how to bring the variables from one userform to another; now I am having issues with the naming of the dynamically created textboxes. Well, really I know how to name them, but can't figure out how to use the textboxes' .Text attributes as input to cells in the Excel document. I have spent upwards of six hours searching the internet for an explanation of how to do this that I can apply to my project, so now I'm looking for personalized help.

Here's the options I have considered:

Arrays
Controls.Add("txtBoxName" & i) in a For...Next loop
    I have actually tried the For...Next loop with Controls.Add,
and had absolutely no luck.

The problem with both of these, while Arrays may be more convenient in the long run, is that (at least as I see it) with both you have to name the textboxes and then call the names, which is a syntax I cannot find anywhere.

Can anyone help? I have no problem posting the relevant code if need be.

Also, if I have overlooked something, feel free to point me in the right direction. I may ask for help understanding it, though! ;)

Thanks, Dudebird47

Once you have named the textboxes, you can refer to them much the same way using formname.controls("textbox1").Text and so on. — Rory
Alright now we know that I can't write comments well! @Rory I tried to use something like this: 'Sub Controls("btnSubmitCourses")_Click()' but I really have no idea what I'm doing. My knowledge is comprised of having a problem and learning new things while searching for a solution. — Nathan Wakefield
Yeah, that won't work! Do you have to create the button at run time? It's a lot easier if you can add it at design time. — Rory
Yeah, I think I can. Since I don't know how many textboxes there will be, I'll create the button at the top and then after the code for creating the textboxes, I might add 'code'btnSubmitNames.Top = (48 * (xA + xB + xC + xD)) + 20'code' where xA, xB, xC, and xD are the numbers of textboxes. This is just to make sure the button ends up at the bottom. Would that work? — Nathan Wakefield

Rory Rory · Accepted Answer · 2014-06-04T15:50:22

Simple example of a button added at run time with code to handle its click event:

Option Explicit
Private WithEvents btn As MSForms.CommandButton

Private Sub btn_Click()
    MsgBox "You clicked me"
End Sub

Private Sub UserForm_Initialize()
    Set btn = Me.Controls.Add("Forms.CommandButton.1", "some_button", True)
    With btn
        .Top = 5
        .Left = 5
        .Width = 75
        .Height = 50
        .Caption = "click me"
    End With
End Sub

Name property of dynamically created textboxes

1 Answers