VBA dynamic UserForm conrols: how to add a number of pairs of label and textbox controls based on sheet columns count

To dynamically add a given number of pairs of label and textbox controls to a form in Excel using VBA, you can follow the steps below:

• Open the VBA editor in Excel by pressing Alt+F11.
• Insert a UserForm by clicking on "Insert" -> "UserForm."
• Design your UserForm by adding a label and textbox controls. 
• Place them in the desired location on the form.
• In the code module for the UserForm, declare the necessary variables

VBA

Dim labelArray() As Object
Dim textBoxArray() As Object
Dim numRows As Integer

• Add a procedure to dynamically create the label and textbox pairs based on the given number of rows.

VBA

Sub AddLabelTextBoxPairs(numPairs As Integer)
    Dim i As Integer
    Dim topPos As Integer
    Dim leftPos As Integer
    
    numRows = numPairs
    
    ' Clear existing controls, if any
    ClearControls
    
    ' Initialize arrays
    ReDim labelArray(1 To numRows)
    ReDim textBoxArray(1 To numRows)
    
    ' Set initial position
    topPos = 10
    leftPos = 10
    
    ' Loop to create label and textbox pairs
    For i = 1 To numRows
        ' Create label
        Set labelArray(i) = Me.Controls.Add("Forms.Label.1", "Label" & i, True)
        With labelArray(i)
            .Caption = ThisWorkbook.Sheets("Sheet1").Cells(1, i).Value ' Use the first row as label text
            .Left = leftPos
            .Top = topPos
            .Width = 100
            .Height = 20
        End With
        
        ' Create textbox
        Set textBoxArray(i) = Me.Controls.Add("Forms.TextBox.1", "TextBox" & i, True)
        With textBoxArray(i)
            .Left = leftPos + 100 ' Adjust position for the textbox
            .Top = topPos
            .Width = 100
            .Height = 20
            .Text = ThisWorkbook.Sheets("Sheet1").Cells(i + 1, i).Value ' Use corresponding cell value for textbox text
        End With
        
        ' Update position for the next pair
        topPos = topPos + 30
    Next i
End Sub

• Optional: Add a procedure to clear existing controls, if any. This is necessary when dynamically adding controls multiple times.

VBA

Sub ClearControls()
    Dim i As Integer
    
    ' Loop to delete existing controls
    For i = 1 To numRows
        Me.Controls.Remove labelArray(i).Name
        Me.Controls.Remove textBoxArray(i).Name
    Next i
    
    ' Reset arrays
    Erase labelArray
    Erase textBoxArray
End Sub

You can call the AddLabelTextBoxPairs procedure from a button click event or another part of your code to dynamically add the label and textbox pairs to the UserForm.

VBA

Sub ClearControls()
Private Sub CommandButton1_Click()
    Dim numPairs As Integer
    numPairs = 5 ' Specify the desired number of pairs
    
    AddLabelTextBoxPairs numPairs
End Sub