Referencing controls on a userform that was created dynamically

AndyGalloway

Board Regular
Joined
Apr 24, 2019
Messages
51
I have created a Userform programmatically and created a series of controls on the form. Now I want to read from/write to one of the text boxes. I have tried everything that I can think of to reference to the control, but I'm having no luck. The control was originally created as part of the process of creating the Userform. The control was created using the following code.

Rich (BB code):
Dim myUserForm As VBComponent
Dim objNewJobTitle As MSForms.TextBox
Dim f As Integer: f = 1
Dim varNewJobTitles() as String

    varNewJobTitles(1) = "Managing Director"
    varNewJobTitles(2) = "Operations Director"
    varNewJobTitles(3) = "Office Manager"

Set myUserForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

With myUserForm
    For f = 1 to 3

    Set objNewJobTitle = myUserForm.Designer.Controls.Add("Forms.TextBox.1", "txtNewJobTitle" & f, True)
        With objNewJobTitle
            .Left = 38  '148
            .Width = 100
            .Height = 18
            .Top = 40 + (18 * f)
            .Value = varNewJobTitles(f)
        End With
    Next

end with


I have tried the obvious of referencing the control using myUserForm.txtNewJobTitle3.Text, but get an error. I have also tried several versions and variations of the following code.

Rich (BB code):
    Set objNewJobTitle = myUserForm.Designer.Controls.Add("Forms.TextBox.1", "txtNewJobTitle" & f, True)
    With objNewJobTitle
        .Value = "Hello"
    End With

    With ThisWorkbook.VBProject.VBComponents("myUserForm").Designer
        .Controls("txtNewJobTitle3").Value = "Hello"
    End With

    With ThisWorkbook.VBProject.VBComponents("myUserForm").Designer
        .Controls("Forms.TextBox.1", "txtNewJobTitle3", True).Text = "Hello"
    End With
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Andy

Where is the code you are trying to reference the dynamically create controls from located?

Is the form loaded/showing?

If so, how are you doing that?
 
Upvote 0
The userform (myUserForm) is created using a script in a module. The userform creates a series of text boxes by iterating through FOR NEXT loop shown in the first code above. As well as a series of text boxes, one for each job title, next to each text box is a button called "btnMoveUp(f)". The code for each button is created dynamically at the time the userform is created, by accessing the CodeModule as in the code below. Currently, the code in the click event for each button returns the test message for example "Operated move up button1" in a messagebox and this work successfully. I will replace this test code with the functional code when i figure this referencing problem out. The actual mechanism for moving a text box up one position involves swapping the "Text" or "Value" in the txtNewJobTitle2 text box for the "Text" or "Value" in the txtNewJobTitle1 text box. To do this, I need the code in the button's click event to be able to reference the relevant text boxes. This is what I am struggling to do. The second code block above shows three attempts (out of many dozens) to do this. None of these attempts work. The code is in the click event of the "Move Up" button in the dynamically created myUserForm.

Code:
    For f = 1 To varListCount
        With myUserForm.CodeModule
        myString1 = "    MsgBox ""Operated move up button " & f & """, vbOKOnly, ""Success!"""
            
            z = .CountOfLines
            .InsertLines z + 1, "Sub btnMoveUp" & f & "_Click()"
            .InsertLines z + 2, "    "
            .InsertLines z + 3, myString1
            .InsertLines z + 4, "    "
            .InsertLines z + 5, "End Sub"
        End With
    Next
 
Last edited:
Upvote 0
Andy

Instead of dynamically creating a userform why not dynamically create the controls on an existing userform and then use a class module to handle the events of the controls?
 
Upvote 0
Thanks for your input. I started to look into that approach but cannot see how it will help me achieve what I ultimately want. It will be better for my final application if I can understand how to access controls on a dynamically created userform. I'm convinced there is a way, it involves referring to the controls using the names with which that they were originally created as in the first code block above. I'm fairly sure one of my codes above are close to the solution, but it all depends n quotation marks, dots and commas all being in the right place and I just can't figure it out.
 
Upvote 0
Andy

How would using a class module not work for you?

Can you post your entire code for creating the userform?

Have you considered using both methods?

PS How about using a listbox?
 
Last edited:
Upvote 0
You're making this far harder than it needs to be by not following Norie's suggestion.

To refer to controls on a loaded userform, you need a reference to that form, then simply refer to its Controls property and pass the name of the control - e.g. someuserform.controls("control name")
 
Upvote 0
So, for example, could I get the caption of a button using the following code? Because I still get an error when I use this approach. However, it is now an error 438 not an error 19 - "Run-time error '438': Object doesn't support this property or method."

Code:
strString = myUserForm.Controls("btnCancel").Caption
 
Upvote 0
I wonder about the use of the term "dynamically created". In the code posted, you are creating using the Developer object. This does not create a run-time control (that stops existing when the userform is unloaded), but creates a static control that will be there the next time a different instance of the userform is shown.

Once you have created this control, you can find its name, etc. and refer to it as you would a control created from the VBEditor/toolbox.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top