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]

Dear Kyle123, With your help I'm making progress with this. I've copied the above code into the click event of the "Reset" button and changed a few things around so that I am copying "varOldJobTitles()" into each text box in turn. It doesn't work. Immediately after the "For Each" line it skips to the line after "Next". I have posted the entire code below. Note 1 - I have not created the varNewJobTitles() array because it is a global array in my application. Note 2 - Similarly, I have not re-dimensioned the array because it will not change after it is created. Adding job titles will be a completely separate routine.

Sub btnReset_Click()
    
    Dim jcc As Variant: jcc = 1
    Dim x As Integer: x = 1
    Dim jobControlHandler As JobChangeControl
    Dim r As Integer: r = 1

    r = MsgBox("Are you sure you want to reset this form? You will lose all changes you have made.", _
    vbQuestion + vbOKCancel, "Are you sure?")
    
    If r = vbOK Then
        handlers.Add jobControlHandler
        Set handlers = New Collection
        For Each jcc In handlers
            jcc.JobTitle.Text = varOldJobTitles(x)
            jcc.UnCheck.Value = True
            x = x + 1
        Next jcc
    End If
   
    MsgBox Join(varOldJobTitles, "; ")
    
End Sub

 

[Kyle123]

Post all of your actual code from the UserForm please, what you’ve written above makes little sense to me I’m afraid

 

[AndyGalloway]

All code in the UserForm3

Dim handlers As Collection

Sub btnCancel_Click()
    
     Call Cancel_Userform
    
End Sub
Sub Cancel_Userform()

    If Me.btnSave.Enabled = True Then

        r = MsgBox("Are you sure you want to cancel this operation without saving any changes?", _
            vbQuestion + vbOKCancel, "Are you sure?")
        If r = vbOK Then
            'UserForm3.Activate
            UserForm3.Hide
            MsgBox "Changes to Job Title re-ordering cancelled.", vbInformation + vbOKOnly, "Operation Cancelled"
            ActiveSheet.Protect Password:=strPass
            End
        Else
            ActiveSheet.Protect Password:=strPass
        End If
    Else
        UserForm3.Hide
    End If
        
End Sub
Sub btnReset_Click()
    
    Dim jcc As Variant: jcc = 1
    Dim x As Integer: x = 1
    Dim jobControlHandler As JobChangeControl
    Dim r As Integer: r = 1

    r = MsgBox("Are you sure you want to reset this form? You will lose all changes you have made.", _
    vbQuestion + vbOKCancel, "Are you sure?")
    
    If r = vbOK Then
        handlers.Add jobControlHandler
        Set handlers = New Collection
        For Each jcc In handlers
            jcc.JobTitle.Text = varOldJobTitles(x)
            jcc.UnCheck.Value = True
            x = x + 1
        Next jcc
    End If
   
    MsgBox Join(varOldJobTitles, "; ")
    
End Sub
Private Sub UserForm_Initialize()

Dim AppXCenter, AppYCenter As Long

Dim f As Integer
Dim i As Integer: i = 1
Dim strPath As String
Dim z As Long
Dim n As Integer
    
    Dim x As Long
    Dim y As Long
    Dim lblOrder As MSForms.Label
    Dim txtBox As MSForms.TextBox
    Dim chkCorrect As MSForms.CheckBox
    Dim btn As MSForms.CommandButton
    Dim jobControlHandler As JobChangeControl
    
    AppXCenter = Application.Left + (Application.width / 2)
    AppYCenter = Application.Top + (Application.height / 2)
    
'   **** Test data to test the code ****

    varListCount = 5
    ReDim varOldJobTitles(varListCount)
    ReDim varNewJobTitles(varListCount)
    ReDim varOrder(varListCount)
    
    varOldJobTitles(1) = "Managing Director"
    varOldJobTitles(2) = "Operations Director"
    varOldJobTitles(3) = "Finance Director"
    varOldJobTitles(4) = "Sales Manager"
    varOldJobTitles(5) = "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)
        .BackColor = &H8000000F
        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
    
    ' Create the Order label
'    Set objLabel = myUserForm.Designer.Controls.Add("Forms.Label.1", "lblOrderTitle", True)
'    With objLabel
'        .Caption = "Order"
'        .Left = 6
'        .width = 40
'        .height = 12
'        .Top = 30
'        .Font.Bold = True
'        .Font.Name = "Tahoma"
'        .Font.Size = 9
'        .TextAlign = fmTextAlignCenter
'    End With

    ' Create the New Job Title label
'    Set objLabel = myUserForm.Designer.Controls.Add("Forms.Label.1", "lblNewJobTitle", True)
'    With objLabel
'        .Caption = "Job Title"
'        .Left = 52
'        .width = 72
'        .height = 12
'        .Top = 30
'        .Font.Bold = True
'        .Font.Name = "Tahoma"
'        .Font.Size = 9
'        .TextAlign = fmTextAlignCenter
'    End With
        
    ' Create the Move Up label
'    Set objLabel = myUserForm.Designer.Controls.Add("Forms.Label.1", "lblMoveUp", True)
'    With objLabel
'        .Caption = "Move Up"
'        .Left = 156
'        .width = 48
'        .height = 12
'        .Top = 30
'        .Font.Bold = True
'        .Font.Name = "Tahoma"
'        .Font.Size = 9
'        .TextAlign = fmTextAlignCenter
'    End With

    Set handlers = New Collection
    
    For x = 1 To varListCount
        Set lblOrder = Me.Controls.Add("Forms.Label.1")
        Set txtBox = Me.Controls.Add("Forms.TextBox.1")
        Set chkCorrect = Me.Controls.Add("Forms.Checkbox.1")
        Set btn = Me.Controls.Add("Forms.CommandButton.1")
        Set jobControlHandler = New JobChangeControl
        Set jobControlHandler.OrderNo = lblOrder
        Set jobControlHandler.JobTitle = txtBox
        Set jobControlHandler.UnCheck = chkCorrect
        Set jobControlHandler.MoveButton = btn
                
        With lblOrder
            .Caption = x
            .Left = 21
            .width = 10
            .height = 18
            .Top = 40 + (18 * x) + 2
        End With
        
        With txtBox
            .Left = 38
            .width = 100
            .height = 18
            .Top = 40 + (18 * x)
            .Value = varNewJobTitles(x)
        End With
        
        With chkCorrect
            .Left = 144
            .width = 12
            .height = 18
            .Top = 40 + (18 * x)
            .Value = True
            .Enabled = False
        End With
        
        With btn
            .Left = 164
            .width = 24
            .height = 18
            .Top = 40 + (18 * x)
            .Caption = "^"
            If x = 1 Then
                .Enabled = False
            End If
        End With
        
        handlers.Add jobControlHandler
        
        If x > 1 Then
            Set jobControlHandler.LevelAbove = handlers.Item(handlers.Count - 1)
        End If
        
    Next x
    
    Me.btnReset.Top = 95 + (18 * varListCount)
    Me.btnSave.Top = 95 + (18 * varListCount)
    Me.btnCancel.Top = 95 + (18 * varListCount)
    
    Me.btnReset.Enabled = False
    Me.btnSave.Enabled = False

End Sub

Code in the JobChangeControl class

Public WithEvents OrderNo As MSForms.Label
Public WithEvents JobTitle As MSForms.TextBox
Public WithEvents UnCheck As MSForms.CheckBox
Public WithEvents MoveButton As MSForms.CommandButton
Public LevelAbove As JobChangeControl

Private Sub MoveButton_Click()
    Dim tmp As String
    Dim x, y As Integer
    tmp = LevelAbove.JobTitle.Value
    LevelAbove.JobTitle = JobTitle.Value
    JobTitle.Value = tmp
    
    ' Swap the job titles in the varNewJobTitles() array
    x = CInt(LevelAbove.OrderNo.Caption)   ' Row above
    y = CInt(OrderNo.Caption)              ' Clicked Row
    varNewJobTitles(x) = LevelAbove.JobTitle.Value
    varNewJobTitles(y) = JobTitle.Value
    
    If varNewJobTitles(x) = varOldJobTitles(x) Then
        LevelAbove.UnCheck.Value = True
    Else
        LevelAbove.UnCheck.Value = False
    End If
    
    If varNewJobTitles(y) = varOldJobTitles(y) Then
        UnCheck.Value = True
    Else
        UnCheck.Value = False
    End If
    
    UserForm3.btnReset.Enabled = True
    UserForm3.btnSave.Enabled = True
    
'    Debug.Print "New Job Title " & x & " is " & varNewJobTitles(x)
'    Debug.Print "New Job Title " & y & " is " & varNewJobTitles(y)

End Sub

 

[Norie]

Andy

Instead of multiple textboxes for the job titles why not use a listbox?

You could add code do allow the titles to be dragged and dropped to move their position in the list.

 

[AndyGalloway]

I thought this would be easier!!! This is being written for others who have decided that this will present the best visual interface.

Besides, we're almost there. I just need to to copy the value of each text box to it's corresponding varNewJobTitle() variable when the "Save" button is pressed and copy the varOldJobTitles() variables to the corresponding text boxes when the "Reset" button is pressed. The latter could be achieved by deleting all the text boxes and re-creating them. It would just be easier to update the value of each text box if we can address them either directly or from top to bottom.

 

[Kyle123]

Why don't you just run the initialize routine again?

Though, for the record, I do agree with Norie

 

[AndyGalloway]

I can run the initialize routine again, I thought there might be a more elegant way of doing it. What about the "Save" routine - populating all of the varNewJobTitles() vatiables with the contents of the text boxes in order? Either that, or identify from the CheckBox which entries have been changed, and only copy those to their corresponding variables.

 

[AndyGalloway]

This appears to do the job. Can you see anything wrong that might trip me up later?

Private Sub btnSave_Click()

    Dim c As Control
    Dim x As Integer: x = 1
    For Each c In UserForm3.Controls
        If TypeName(c) = "TextBox" Then
            varNewJobTitles(x) = c.Value
            Debug.Print varNewJobTitles(x)
            x = x + 1
        End If
    Next
    
    MsgBox Join(varNewJobTitles, "; ")

End Sub

 

[Kyle123]

You'd be better looping through the handlers as I did in the example, you can guarantee the order then, I guess they should do, but I don't know for certain that enumerating over the controls property returns controls in the order that they were added.

 

[AndyGalloway]

That was my thoughts as well, but I just couldn't get the handlers to work. As I stated earlier in post #22, I can't make it work. I'm not familiar enough with handlers or how they work to use them to address the controls that I want. I'm not even sure if what I've just said makes any sense!

Here is the code for the save, reset and query_close routines:

Sub btnReset_Click()
    
Dim c As Control
Dim x As Integer: x = 1
Dim r As Integer: r = 1

    r = MsgBox("Are you sure you want to reset this form?" & Chr(10) & Chr(10) & "You will lose all changes you have made.", _
    vbQuestion + vbOKCancel, "Are you sure?")
    
    If r = vbOK Then
        For Each c In UserForm3.Controls
            If TypeName(c) = "TextBox" Then
                c.Value = varOldJobTitles(x)
                x = x + 1
            ElseIf TypeName(c) = "CheckBox" Then
                c.Value = True
            End If
        Next
    
    Me.btnReset.Enabled = False
    Me.btnSave.Enabled = False
   
'    MsgBox Join(varOldJobTitles, " | ")

    End If
    
End Sub

Private Sub btnSave_Click()

Dim c As Control
Dim x As Integer: x = 1

    For Each c In UserForm3.Controls
        If TypeName(c) = "TextBox" Then
            varNewJobTitles(x) = c.Value
            x = x + 1
        End If
    Next
    
'    MsgBox Join(varNewJobTitles, " | ")

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Dim r As Integer

    If Me.btnSave.Enabled = True Then
        r = MsgBox("Are you sure you want to cancel this operation without saving any changes?", _
            vbQuestion + vbOKCancel, "Are you sure?")
        If r = vbOK Then
            'UserForm3.Activate
            UserForm3.Hide
            MsgBox "Changes to Job Title re-ordering cancelled.", vbInformation + vbOKOnly, "Operation Cancelled"
            ActiveSheet.Protect Password:=strPass
            End
        Else
            ActiveSheet.Protect Password:=strPass
            Cancel = True
        End If
    Else
        UserForm3.Hide
    End If
    
End Sub