Add Controls to UserForm, Excel VBA

Here’s an example of VBA code that creates a UserForm containing a Label, a TextBox, and a CommandButton. I’ll also explain each part of the code to help you understand how it works.

Sub CreateUserForm()
    ' Declare a variable for the UserForm
    Dim myForm As Object
    Set myForm = CreateObject("Forms.UserForm.1")
    ' Add a Label
    Dim lbl As Object
    Set lbl = myForm.Controls.Add("Forms.Label.1", , True)
    lbl.Caption = "Enter your name:"
    lbl.Top = 10
    lbl.Left = 10
    lbl.Width = 200
    ' Add a TextBox
    Dim txt As Object
    Set txt = myForm.Controls.Add("Forms.TextBox.1", , True)
    txt.Top = 30
    txt.Left = 10
    txt.Width = 200
    ' Add a CommandButton
    Dim btn As Object
    Set btn = myForm.Controls.Add("Forms.CommandButton.1", , True)
    btn.Caption = "Submit"
    btn.Top = 60
    btn.Left = 10
    btn.Width = 100
    ' Add an event to the button (clicking the button will close the form)
    btn.OnClick = "btnClick"
    ' Show the UserForm
    myForm.Show
End Sub
Sub btnClick()
    MsgBox "You entered: " & ActiveSheet.TextBox1.Value
    ' Close the UserForm after the button is clicked
    Unload ActiveForm
End Sub

Explanation of the Code

1. Creating the UserForm:
   • The code starts by creating a UserForm object using the CreateObject(« Forms.UserForm.1 ») method.
2. Adding a Label:
   • A Label control is added to the UserForm using the Controls.Add method.
   • The text to display is set with lbl.Caption = « Enter your name: ».
   • The position and size of the label are set using the Top, Left, and Width properties.
3. Adding a TextBox:
   • Similarly, a TextBox control is added using Controls.Add(« Forms.TextBox.1 », , True).
   • The TextBox allows the user to input a text, in this case, a name.
4. Adding a CommandButton:
   • A CommandButton control is added for the user to interact with.
   • The button text is set with btn.Caption = « Submit ».
   • The position and size of the button are defined as well.
5. Handling the Button Click Event:
   • The button has an OnClick event that runs a procedure (btnClick) when clicked.
   • In the btnClick procedure, a message box displays the text entered in the TextBox, and then the UserForm is closed using Unload ActiveForm.

Conclusion

This code creates a simple form with a label, a text box, and a button. When the button is clicked, the text entered in the text box is displayed in a message box, and the form is closed. This type of form can be used in a user interface to collect information and perform actions based on user input.