VBA Textboxes created at runtime cannot be referenced

Question

I have some code that creates a whole line of textboxes at run-time, the textboxes use the dimensions of the pre-established boxes to create a whole line of textboxes each time 'Add Line' is clicked and it looks like this -

This is done with the following code -

Public Sub AddLine_Click()
Static i As Integer
Dim txtbox As Object


DocNameArr = Array("Type", "Status", "Reference", "Units", "Quantity", "ValidityDate", "IssuingAuthority", "Reason")

AbsoluteHeight = Me.DocType.Top
prevWidth = 0

Dim cCont As Control
contCount = 1

For Each cCont In Me.Controls
If TypeName(cCont) = "TextBox" And InStr(1, cCont.Name, "Type") = 1 Then
contCount = contCount + 1
End If
Next cCont


     For Each DocName In DocNameArr
     Set txtbox = DocumentsForm.Controls.Add("Forms.Textbox.1", True)
     currTxtbox = "Doc" & DocName
     
     txtboxWidth = DocumentsForm.Controls(currTxtbox).Width
     
            With txtbox
            .Name = DocName & contCount
            .Left = 10.5 + prevWidth
            .Height = 26.25
            .Top = 47.25 + (26.25 * contCount)
            .Width = txtboxWidth
            .SpecialEffect = fmSpecialEffectFlat
            .BorderStyle = fmBorderStyleSingle
            .BorderColor = &H80000000
            End With
            
    prevWidth = prevWidth + txtboxWidth
    Next
End Sub

I name the textboxes at runtime as per the code and each separate box in each line of boxes has a number to identify the line and a name to identify the box. This is all fine until I want to save the information that the user enters into the box.

If I reference a textbox directly via name that was created during run-time and as a test insert text directly into it, it drops an Object Required error telling me that the object of that name cannot be found. For example based on the code, I should have a "Type1" box created on the first line, but I can't reference it.

For now, for testing purposes all I've done is reference the textbox directly to see if its accessible via name by doing

Type1.Value = "Test"
Type2.Value = "Test"
Type3.Value = "Test"

Just trying to parse the word "Test" into any of the textboxes that are created.

How do I save the data of the textbox if its created as above during run-time?

I don't see why it doesn't let me call the textbox via the name its getting allocated at run-time, I output all controls into a msgbox and it comes up with "" for all the run-time named controls? Where am I going wrong here.. any help would be appreciated.

Answer 1

Loop through the Controls collection and pull out the TextBox you want eg

Function GetTextBox(sName As String) As MSForms.TextBox
    Dim ctrl As Control
    For Each ctrl In Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            If ctrl.Name = sName Then
                Set GetTextBox = ctrl
                Exit Function
            End If
        End If
    Next ctrl
End Function

... pass the name of the TextBox into this Function and, so long as it exists in the UserForm, it will return the TextBox object to you