VBA UserForm

What is Excel VBA UserForm?

Excel VBA (Visual Basic for Applications) provides a Replace function that allows you to replace occurrences of a specific string within a text string. It is possible to implement VBA Replace string in a text file.

Consider the following example:

The VBA code changes the caption in “Label1” when we click the button to “Button Clicked.” If we run the VBA code, we get the output shown below:

What is Excel VBA UserForm?

VBA UserForm is a graphical interface tool in Excel. It allows the creation of custom dialog boxes for data entry and user feedback.
Various controls like buttons and textboxes enhance its functionality. The VBA code behind the UserForm enables dynamic behavior.
UserForms are created and customized using the VBA editor in Excel. They provide a visual and interactive way to interact with users.
UserForms can collect data, display information, and perform actions.
Controls like labels, buttons, combo boxes, and list boxes can be added to the UserForm.

How to Create UserForm?

To create VBA UserForm, let us see the steps below:

Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and sub-procedures. Click the “Insert” and “UserForm” buttons to create a new UserForm or blank page.
Customize the UserForm according to your preferences.

VBA UserForm provides a ToolBox with which you can customize it.

Here, we have chosen three labels, three textboxes, and one Command Button for the above user form and spaced them as shown above to accept user input.
If you drag and drop Label, you’ll only get Label1. To change the label name, we right-click and select the “Properties” of the given label.

Here, we can change the label’s name by selecting “Caption” and editing the name in the Properties section.

Similarly, change the label values for the other elements.
Once we’ve created our UserForm, we must define a subroutine to define the parameter of the “Submit” button. Double-click on the element to define the properties of the button, which will automatically define a subroutine.
Define three variables to accept values for name, age, and gender.
Let VBA read the values given by the user with the textboxes provided by the user.

We can get the values from TextBox with the command TextBox1.Text, where TextBox1 is the name of the specific textbox element.

We convert age as an integer since all the datatypes in the textbox are given as VBA String.
Print the values in a message box and define Name, Age, and Gender in different lines using the vbCrLf function.

Code:

Private Sub CommandButton1_Click()
Dim name As String
Dim age As Integer
Dim gender As String
name = TextBox1.Text
age = Val(TextBox2.Text)
gender = TextBox3.Text
MsgBox “Name: ” & name & vbCrLf & “Age: ” & age & vbCrLf & “Gender: ” & gender, vbInformation, “User Information”
End Sub
Run the VBA code. It will run the UserForm.

Enter the details and click “Submit.”

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Formatting UserForm

To format a UserForm in Excel VBA, you can use the Properties window to change the appearance and behavior of the form and its controls. To do so, click the Create UserForm button.

It will create a brand new userform. You can drag the userform to adjust its size. View its properties by selecting the element and right-clicking it.

With this, we can change the names in the window by editing the properties window.

Select the UserForm “BackColor” property to change the color of the UserForm.

As you can see, the UserForm color has changed. Similarly, to change the color of the elements, select the element’s properties and change its color.

To change the font, go to the properties window and select the font option.

It will open a new window where you can change the font type, size (bold, italic, etc.), and the size of the letter.

It shows how dynamic and customizable the UserForm is. Let us now view some ways to format and use VBA UserForms.

Examples

Given below are some examples which show the diverse uses of the UserForm.

Example #1

Suppose you want to create a login UserForm which accepts Username and Password as inputs. If it is correct, it returns- “Login Successful.” Else, it denies access.

Step 1: Create a new UserForm.

Step 2: Add the necessary elements. We’re creating a VBA UserForm label and a VBA UserForm TextBox.

Step 3: Customize the textboxes and Label names by editing the Properties section of the UserForm and its elements.

You will get the following result.

Note: This is just an example. You can customize your userform as you like in your Excel VBA project.

Step 4: Double-click the “Submit” button to create a subroutine to define the parameters for the userform to accept or deny a login.

Step 5: Initialize two string values to record the correct username and password.

Step 6: Initialize another set of strings to accept the values given by the user.

It takes in the username and password values given by the user in the UserForm textbox.

Step 7: Initialize an If-Else statement to check if the entered username and password matches the username stored in the database. It either accepts or denies the request log.

vbExclamation provides a Message Box with the exclamation mark icon with the header “Login Status.”

It returns a Message Box with the header “Login Status” and the username when correct. vbInformation provides the information icon that is commonly used.

Code:

Private Sub CommandButton1_Click()
Dim correctUsername As String
Dim correctPassword As String
correctUsername = “user”
correctPassword = “XXXXX”
Dim enteredUsername As String
Dim enteredPassword As String
enteredUsername = TextBox1.Text
enteredPassword = TextBox2.Text
If enteredUsername = correctUsername And enteredPassword = correctPassword Then
MsgBox “Login successful! Welcome, ” & correctUsername & “!”, vbInformation, “Login Status”
Else
MsgBox “Login failed. Incorrect username or password.”, vbExclamation, “Login Status”
End If
End Sub

Step 8: Run the userform.

After clicking the “Submit” button we get

Example #2

Suppose you have a table containing the names of people, and you want to implement the VBA Search function. You can print the number of times a name appears in the list, and if the name does not exist, return a message for no name in the list.

It can be implemented by embedding a search function in the UserForm. Let us see how we can do that below.

Step 1: Create a new UserForm.

Step 2: Add the necessary elements. Here, we’re creating a VBA UserForm label, a VBA UserForm ListBox, and a VBA UserForm TextBox.

The resulting UserForm will be as follows:

Step 3: By going to the Properties tab, format the UserForm per your preferences.

After selecting the elements, you can customize all the elements by selecting the color, font size, and font display in the properties window.

Step 4: After customization, the UserForm, looks as follows.

Step 5: Double-click the UserForm to open the Editor and implement the search function.

Step 6: Define what you want to be displayed in the Listbox.

Step 7: Define the TextBox function, which will call the Search subroutine when text is input.

Step 8: Initialize a subroutine PerformSearch to search a given table in Excel.

Step 9: Clear any previous ListBox values and define a range value to hold in the size of the table.

Step 10: Find the range of the table by selecting the Sheet name and range value starting from and finding the last non-empty row in column “A” using the xlUp function.

Step 11: Initialize a For loop to search the string value in the textbox input.

Here,

If InStr(1, cell.Value, searchTerm, vbTextCompare) > 0 Then
ListBox1.AddItem cell.Value

Using the InStr function to check letter values, we check whether it returns a value greater than 0 and add that value to the VBA UserForm ListBox using AddItem.

If no letters match the list, it returns “No matching results found.”

Code:

Private Sub TextBox1_Change()
PerformSearch TextBox1.Text
End Sub

Private Sub UserForm_Click()
ListBox1.ColumnCount = 1
ListBox1.ColumnWidths = “20”
ListBox1.List = Array(“Search results will appear here.”)
End Sub

Private Sub PerformSearch(ByVal searchTerm As String)
ListBox1.Clear
Dim dataRange As Range
Set dataRange = ThisWorkbook.Sheets(“Sheet1”).Range(“A1:A10” & ThisWorkbook.Sheets(“Sheet1”).Cells(Rows.count, “A”).End(xlUp).row)
Dim cell As Range
For Each cell In dataRange
If InStr(1, cell.Value, searchTerm, vbTextCompare) > 0 Then
ListBox1.AddItem cell.Value
End If
Next cell
If ListBox1.ListCount = 0 Then
ListBox1.AddItem “No matching results found.”
End If
End Sub

Step 12: Run the UserForm and view the Output.

VBA Code

Similarly, formatting of the UserForm can be done by editing the VBA code with the suffix:

Me.propertyname

Where propertyname is the property of the element selected, and “Me” is used to describe the UserForm.

Consider an example where by choice, we can change the background color of the UserForm to the color we pick.

The VBA Code can be edited as such:

Here, we choose the Me.BackColor property in VBA code to change the color of the UserForm and give multiple options to change it with the help of the Switch-Case concept.

By running the VBA code, we get the following:

This way, we can demonstrate how to use VBA code to format the UserForm.

Important Things to Note

Use clear and descriptive labels to guide users and improve understanding.
Implement VBA error handling to handle unexpected situations and provide meaningful error messages to users.
Avoid adding too many controls or information on a single UserForm to maintain clarity.
Always validate user inputs to ensure accurate data entry and prevent errors.
Stick to familiar UI patterns to avoid confusing users.
Avoid using labels or instructions that might confuse users about what to do.

Frequently Asked Questions (FAQs)

1. How to call UserForm in Excel VBA?

To perform VBA UserForm Show, we use the command

• UserForm1.Show

You can change “UserForm1” to the custom name you’ve applied in your VBA UserForm.

2. How do you close a UserForm in VBA?

To perform VBA UserForm close, we use the following code.

• Unload Me

It will unload or trigger a VBA UserForm Close function.

3. Why is VBA UserForm.Show not working?

• The UserForm is not properly created or named in the VBA editor.
• The UserForm is hidden or not visible due to incorrect settings.
• The code to show the UserForm is not executed or not called at the right time.
• There is a typo or error in the UserForm name or the code itself.
• There could be an error in the code before the UserForm.Show statement.

4. Which event is triggered when a user closes UserForm?

When VBA UserForm Close is triggered, it triggers the event “UserForm_QueryClose.”

Download Template

This article must be helpful to understand the VBA UserForm, with its features and examples. You can download the template here to use it instantly.

VBA UserForm Download

VBA UserForm