Create Dynamic UserForm Controls with Excel VBA

VBA Code for Dynamic UserForm Controls

This code will create a UserForm with labels, textboxes, and a button dynamically when executed.

Option Explicit
Dim NewForm As Object
Dim TxtBox As Object
Dim Lbl As Object
Dim Btn As Object
Sub CreateDynamicUserForm()
    Dim i As Integer
    Dim CodeModule As Object   
    ' Create the UserForm dynamically
    Set NewForm = ThisWorkbook.VBProject.VBComponents.Add(3) ' 3 corresponds to a UserForm   
    With NewForm
        .Properties("Caption") = "Dynamic UserForm"
        .Properties("Width") = 300
        .Properties("Height") = 250
    End With   
    ' Loop to create multiple TextBoxes and Labels
    For i = 1 To 3
        ' Create Label
        Set Lbl = NewForm.Designer.Controls.Add("Forms.Label.1", "Label" & i, True)
        With Lbl
            .Caption = "Label " & i
            .Left = 20
            .Top = 20 + (i - 1) * 30
            .Width = 80
        End With       
        ' Create TextBox
        Set TxtBox = NewForm.Designer.Controls.Add("Forms.TextBox.1", "TextBox" & i, True)
        With TxtBox
            .Left = 110
            .Top = 20 + (i - 1) * 30
            .Width = 150
        End With
    Next i   
    ' Create Submit Button
    Set Btn = NewForm.Designer.Controls.Add("Forms.CommandButton.1", "btnSubmit", True)
    With Btn
        .Caption = "Submit"
        .Left = 100
        .Top = 120
        .Width = 100
    End With   
    ' Add Button Click Event using CodeModule
    Set CodeModule = NewForm.CodeModule
    With CodeModule
        Dim Code As String
        Code = "Private Sub btnSubmit_Click()" & vbCrLf & _
               "   MsgBox ""You clicked Submit!""" & vbCrLf & _
               "End Sub"
        .InsertLines .CountOfLines + 1, Code
    End With
    ' Show the form dynamically
    VBA.UserForms.Add(NewForm.Name).Show
End Sub

Explanation of the Code

1. Creating the UserForm Object Dynamically

Set NewForm = ThisWorkbook.VBProject.VBComponents.Add(3) ' 3 corresponds to a UserForm

• The VBProject.VBComponents.Add(3) function dynamically creates a new UserForm in the workbook’s VBA project.
• The form is stored in the NewForm variable.

2. Setting the UserForm Properties

With NewForm
    .Properties("Caption") = "Dynamic UserForm"
    .Properties("Width") = 300
    .Properties("Height") = 250
End With

• The caption of the form is set to « Dynamic UserForm ».
• The width and height of the form are defined.

3. Looping to Add Labels and Textboxes

For i = 1 To 3
    ' Create Label
    Set Lbl = NewForm.Designer.Controls.Add("Forms.Label.1", "Label" & i, True)
    With Lbl
        .Caption = "Label " & i
        .Left = 20
        .Top = 20 + (i - 1) * 30
        .Width = 80
    End With

• A loop runs from 1 to 3 to create multiple labels and textboxes.
• NewForm.Designer.Controls.Add(« Forms.Label.1 », « Label » & i, True) dynamically adds a label.
• Caption is set to « Label i », and the label is positioned accordingly.

' Create TextBox
    Set TxtBox = NewForm.Designer.Controls.Add("Forms.TextBox.1", "TextBox" & i, True)
    With TxtBox
        .Left = 110
        .Top = 20 + (i - 1) * 30
        .Width = 150
    End With
Next i

   Similarly, textboxes are added dynamically next to the labels.

4. Adding a Submit Button

Set Btn = NewForm.Designer.Controls.Add("Forms.CommandButton.1", "btnSubmit", True)
With Btn
    .Caption = "Submit"
    .Left = 100
    .Top = 120
    .Width = 100
End With

• A button named « btnSubmit » is created.
• The caption « Submit » is added, and it is positioned appropriately.

5. Adding VBA Code to Handle Button Click

Set CodeModule = NewForm.CodeModule
With CodeModule
    Dim Code As String
    Code = "Private Sub btnSubmit_Click()" & vbCrLf & _
           "   MsgBox ""You clicked Submit!""" & vbCrLf & _
           "End Sub"
    .InsertLines .CountOfLines + 1, Code
End With

• The CodeModule object is used to insert VBA code dynamically into the new UserForm.
• The « btnSubmit_Click » event is added, displaying a message box when the button is clicked.

6. Displaying the UserForm

VBA.UserForms.Add(NewForm.Name).Show

• The dynamically created UserForm is shown.

Enhancements & Customization

1. Dynamic Control Creation:
   • The number of textboxes and labels can be modified dynamically by changing the loop limit.
2. Event Handling Enhancements:
   • Additional events such as text change (TextBox_Change) or exit (TextBox_Exit) can be added dynamically.
3. More Controls:
   • Other controls such as ComboBoxes, CheckBoxes, and Frames can be added using a similar approach.
4. Storing Form Data:
   • The text entered into textboxes can be stored in a worksheet upon clicking Submit.