Referencing controls on a userform that was created dynamically

[AndyGalloway]

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.

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.

    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

 

[AndyGalloway]

Kyle123. Thanks for the link, but it is a link to an earlier post that I made about how to create the UserForm. Creating the UserForm works very well. The trick was to save the UserForm immediately after creating it, then it is available to Show.

What I am trying to do, is correlate quite a lot of data held in various sheets and create a form to display that data. At the time of requesting the form be created, I don't know how many controls or what type of controls will be needed on the form. Therefore, what I do is look for an instance of "myUserForm" and, if it exists, delete it and create a new "myUserForm" from scratch. The alternative would be to open the userform and delete all the controls on the form and then replace them. I think it is easier and quicker to delete the form and create a new one. However, I have run into a problem where I cannot reference the newly created controls on the newly created userform.

I have looked in the Project Explorer at one of the properties I am trying to change but am unable to, the "Caption". I notice that the caption in "UserForm1" (permanent form) has the properties of:
Property Caption As String
Member of MSForms.Frame

Clicking on "MSForm" takes me to the MSForms Library within the Project Explorer.

Whereas the Caption for "myUserForm" has the properties of:
Property Caption As String
Member of Unknown8._Form

Clicking on "Unknown 8" I get a message "Cannot jump to 'Unknown 8' because it is in the library 'Unknown 8' which is not currently referenced.

I don't know what has caused this or how to fix it. It may be a red herring or it may the very thing that is causing me grief.

Thanks for all your help so far. I appreciate it.

 

[Kyle123]

If you actually read the earlier posts in this thread and my code in the other, you’ll see my code does exactly that. Since the controls are added properly, they also aren’t there when the form is reopened, rather they’re added from scratch. No need to delete anything

 

[AndyGalloway]

Sorry Kyle123, I recall that I did try your solution on the other thread, but felt I was making more progress the way that I was going. I have adapted your code from that thread and it is below. I have used your ButtonHandler and created a similar one called TextHandler. The code below makes a form, populates it with as many text boxes and their corresponding buttons as are required. Now I get to the bit where I got stuck previously. How do I use the ButtonHandler to swap the value in two adjacent text boxes? So, when I click on button number 2, it swaps the text in textbox number 2 and textbox number 1.

Because I can't see how to name each of the text boxes and buttons, I don't know how to write the code behind each button to identify the text boxes to swap values.

Dim handlers As Collection


Private Sub UserForm_Initialize()
    
    Dim x As Long
    Dim y As Long
    Dim btn As MSForms.CommandButton
    Dim txtBox As MSForms.TextBox
    Dim btnHandler As ButtonHandler
    Dim txtHandler As TextHandler
    Dim AppXCenter, AppYCenter As Long
    
    AppXCenter = Application.Left + (Application.width / 2)
    AppYCenter = Application.Top + (Application.height / 2)
    
'   **** Test data for testing the code ****

    varListCount = 3
    ReDim varOldJobTitles(varListCount)
    ReDim varNewJobTitles(varListCount)
    ReDim varOrder(varListCount)
    
    varOldJobTitles(1) = "Managing Director"
    varOldJobTitles(2) = "Operations Director"
    varOldJobTitles(3) = "Office Manager"
    
    For y = 1 To varListCount
        varNewJobTitles(y) = varOldJobTitles(y)
    Next

'   **** End of test data ****
       
    With Me
        .height = 165 + (18 * varListCount)
        .width = 228
        .StartUpPosition = 0
        .Top = AppYCenter - (.height / 2)
        .Left = AppXCenter - (.width / 2)
        If .height > Application.height Then
            .Top = Application.Top
            .height = Application.height
            .KeepScrollBarsVisible = fmScrollBarsVertical
            .ScrollBars = fmScrollBarsVertical
            .ScrollHeight = 130 + (18 * varListCount)
        Else
            .Top = AppYCenter - (.height / 2)
            .KeepScrollBarsVisible = fmScrollBarsNone
            .ScrollBars = fmScrollBarsNone
            .ScrollHeight = 0
        End If
    End With
    
    Set handlers = New Collection
    
    For x = 1 To varListCount
        Set btn = Me.Controls.Add("Forms.CommandButton.1")
        Set btnHandler = New ButtonHandler
        Set btnHandler.CommandButton = btn
        handlers.Add btnHandler
        With btn
            .Left = 164 '274
            .width = 24
            .height = 18
            .Top = 40 + (18 * x)
            .Caption = "^"
            If x = 1 Then
                .Enabled = False
            End If
        End With
        
        Set txtBox = Me.Controls.Add("Forms.TextBox.1")
        Set txtHandler = New TextHandler
        Set txtHandler.TextBox = txtBox
        handlers.Add txtHandler
        With txtBox
            .Left = 38  '148
            .width = 100
            .height = 18
            .Top = 40 + (18 * x)
            .Value = varNewJobTitles(x)
        End With
    Next x


End Sub

 

[Kyle123]

Explain what you actually mean by this:

I don't know how to write the code behind each button to identify the text boxes to swap values.

I suspect the code doesn't go where you think it would

 

[Kyle123]

Actually, I think you just want this:
Class JobChangeControl

Public WithEvents MoveButton As MSForms.CommandButton
Public WithEvents JobTitle As MSForms.TextBox
Public LevelAbove As JobChangeControl


Private Sub MoveButton_Click()
    Dim tmp As String
    tmp = LevelAbove.JobTitle.Value
    LevelAbove.JobTitle = JobTitle.Value
    JobTitle.Value = tmp
End Sub

Userform:

Dim handlers As Collection


Private Sub UserForm_Initialize()
    
    Dim x As Long
    Dim y As Long
    Dim btn As MSForms.CommandButton
    Dim txtBox As MSForms.TextBox
    Dim jobControlHandler As JobChangeControl
    Dim AppXCenter, AppYCenter As Long
    
    AppXCenter = Application.Left + (Application.Width / 2)
    AppYCenter = Application.Top + (Application.Height / 2)
    
'   **** Test data for testing the code ****

    varListCount = 3
    ReDim varOldJobTitles(varListCount)
    ReDim varNewJobTitles(varListCount)
    ReDim varOrder(varListCount)
    
    varOldJobTitles(1) = "Managing Director"
    varOldJobTitles(2) = "Operations Director"
    varOldJobTitles(3) = "Office Manager"
    
    For y = 1 To varListCount
        varNewJobTitles(y) = varOldJobTitles(y)
    Next

'   **** End of test data ****
       
    With Me
        .Height = 165 + (18 * varListCount)
        .Width = 228
        .StartUpPosition = 0
        .Top = AppYCenter - (.Height / 2)
        .Left = AppXCenter - (.Width / 2)
        If .Height > Application.Height Then
            .Top = Application.Top
            .Height = Application.Height
            .KeepScrollBarsVisible = fmScrollBarsVertical
            .ScrollBars = fmScrollBarsVertical
            .ScrollHeight = 130 + (18 * varListCount)
        Else
            .Top = AppYCenter - (.Height / 2)
            .KeepScrollBarsVisible = fmScrollBarsNone
            .ScrollBars = fmScrollBarsNone
            .ScrollHeight = 0
        End If
    End With
    
    Set handlers = New Collection
    
    For x = 1 To varListCount
        Set btn = Me.Controls.Add("Forms.CommandButton.1")
        Set jobControlHandler = New JobChangeControl
        Set jobControlHandler.MoveButton = btn
        With btn
            .Left = 164 '274
            .Width = 24
            .Height = 18
            .Top = 40 + (18 * x)
            .Caption = "^"
            If x = 1 Then
                .Enabled = False
            End If
        End With
        
        Set txtBox = Me.Controls.Add("Forms.TextBox.1")
        Set jobControlHandler.JobTitle = txtBox
        With txtBox
            .Left = 38  '148
            .Width = 100
            .Height = 18
            .Top = 40 + (18 * x)
            .Value = varNewJobTitles(x)
        End With
        
        handlers.Add jobControlHandler
        
        If x > 1 Then
            Set jobControlHandler.LevelAbove = handlers.Item(handlers.Count - 1)
        End If
        
    Next x


End Sub

 

[Norie]

I'm confused now, are we working with a userform created programmatically or an existing userform with controls added dynamically and with classes handling events?

I think someone suggested the latter approach earlier in the thread.

 

[Kyle123]

Looks like Andy's seen the light!

 

[AndyGalloway]

Norie, yes an existing form with controls added dynamically. Not sure how it will affect the rest of the project, but if the old ways don't work, and I'm sure they don't work, and I try again a few more times and they still don't work, then I'm the first to admit that there's a small possibility it doesn't work. Always open to admit when there's a possibility I might not be right. That's what people love about me!

Kyle123, thanks for your input on this code. When I run the code, I get an error 91 on both Set jobControlHandler lines - error '91' Object variable or With block variable not set. I have copied your code faithfully (as far as I can tell). Any additional help on this last little bit would be greatly appreciated.

        Set jobControlHandler.MoveButton = btn


        Set jobControlHandler.JobTitle = txtBox

 

[AndyGalloway]

Kyle123, apologies. The code is working correctly now. It's funny how a small typo can really mess things up!

The final piece of the puzzle is to copy the contents of the JobTitle textbox into it's corresponding variable varNewJobTitle(x), Can you show me how to do that please. This can either be as part of the MoveButton click event or, in the click event of a "Save" button just read all job titles in order, top to button into the varNewJobTitles() array. Whichever you feel would be easier. Again, thanks in advance for your help.

 

[Kyle123]

Like this:

    Dim jcc                  As Variant
    Dim varNewJobTitle()    As String
   
    ReDim varNewJobTitle(0 To handlers.Count - 1)
   
    For Each jcc In handlers
        varNewJobTitle(x) = jcc.JobTitle.Text
        x = x + 1
    Next jcc
   
    MsgBox Join(varNewJobTitle, "; ")