A UserForm is an interface that allows for the organized and friendly display of information based on the user’s needs. This way, the user avoids typing directly on worksheets or databases, which protects the information while simplifying data entry.
UserForms are an excellent choice if your applications are programmed in Excel/VBA, as they can be tailored to user needs and are 100% customizable.
In this tutorial, we will develop a simple user interface that generates a greeting message based on the entered information.
1. How to Create a UserForm in VBA?
First, find the “Insert” option in the Visual Basic Editor (VBE) toolbar, then select “
UserForm.” The default form will be created in the workspace.
The property window for the form will appear at the bottom right of the VBE. This is where we can control the entire
UserForm and its internal elements.
2. Internal Elements of the UserForm – Buttons, TextBox, ComboBox, and Others
These elements allow interaction between the
UserForm and the user. To add them, after creating the form, you must display the toolbox using the “View” option in the VBE.
To add an element, simply select it from the toolbox and drag it to the
UserForm. You can position and resize it as needed using your mouse. Later, you’ll also see that you can adjust it through the properties of each element.Below is a list of the most commonly used elements when creating
UserForms. All elements, without exception, can be configured or updated via programming code.
2.1. Image
This is a non-editable field, typically used to insert an image like a logo.
2.2. Label
This is a non-editable field, typically used to label or explain the function of another element within the
UserForm.
2.3. TextBox
This is an editable element, used for data input. Any value entered via the TextBox will be recognized by VBA as a String. If you want it to be recognized as another type (number, date, etc.), you will need to convert the variable type. You can review the
tutorial on variables for more information.
2.4. ComboBox
This element, also known as a drop-down, is used to house a list of options for the user to choose one from.
2.5. CheckBox
This is a boolean element (it takes True or False values) that can be modified by the user.
2.6. OptionButton
Similar to a
CheckBox, this is another boolean element (it takes True or False values) that can be modified by the user.
2.7. CommandButton
This element is where actions to be executed in the program are assigned, such as running a macro, closing the UserForm, opening a file, and more.
3. Properties of the UserForm and Its Elements
UserForms and their elements have properties that can be modified directly through the properties window in VBA. Some of these properties are common to all elements, such as appearance-related properties (width, height, location), while others are specific to individual elements. Below is a list of properties based on the type of element.
| Property |
Description |
Applies to: |
| (Name) |
The name of the element. Used to identify the element in VBA code. |
All elements |
| Back Color |
Background color |
All elements |
| Caption |
Title text |
UserForm, Label, CheckBox, OptionButton, CommandButton |
| Enabled |
Activation |
All elements |
| Font |
Text font |
UserForm, Label, TextBox, ComboBox, CheckBox, OptionButton, CommandButton |
| Height |
Element height |
All elements |
| Left |
Horizontal location |
All elements |
| Picture |
Image |
UserForm, Image, Label, CheckBox, OptionButton, CommandButton |
| Picture-SizeMode |
Image size adjustment |
UserForm, Image, CommandButton* |
| TextAlign |
Text alignment |
Label, TextBox, ComboBox, CheckBox, OptionButton |
| Top |
Vertical location |
All elements |
| Visible |
Visibility |
Image, Label, TextBox, ComboBox, CheckBox, OptionButton, CommandButton |
| With |
Width |
All elements |
| Word-Wrap |
Text wrapping |
Label, TextBox, CheckBox, OptionButton, CommandButton |
(*) The equivalent property for the
CommandButton is called
Picture Position
4. Scripts and Programming Code to Control the UserForm
The most common VBA action you will perform with the
UserForm is to assign an action to a button. The action could involve generating a message box, closing the form, or performing complex operations. Below is an example of code that executes an action based on the values entered by the user in the form.
To assign an action to a button, right-click the button, then select the “View Code” option. The VBE editor will automatically open the code window for that button, and you can enter the script to be executed when the button is clicked.
The following script takes the input from two TextBox elements and creates a greeting message for the user:
Private Sub CommandButton1_Click()
Dim greeting As String
greeting = "Hello " & TextBox1.Value & " " & TextBox2.Value & "!"
MsgBox greeting, vbInformation, "Greeting"
End Sub
When the button is clicked, the TextBox values will be concatenated to form the greeting, and the result will be shown in a message box.
To close the
UserForm, you can use the following script:
Private Sub CommandButton2_Click()
Unload Me
End Sub
This code will close the form when the button is clicked. The “Unload Me” instruction unloads the UserForm from memory.
Want to learn more?
Contact us…
