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:

VBA UserForm Definition Example - 1
VBA UserForm Definition Example - 2

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:

VBA UserForm Definition Example - 3
Key Takeaways
  • 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:

  1. 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.

    How to Create VBA UserForm - Step 1a

  2. Customize the UserForm according to your preferences.

    How to Create VBA UserForm - Step 2a

    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.

  3. 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.

    How to Create VBA UserForm - Step 3a

    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.

  4. 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.

    How to Create VBA UserForm - Step 4

  5. Define three variables to accept values for name, age, and gender.

    How to Create VBA UserForm - Step 5

  6. Let VBA read the values given by the user with the textboxes provided by the user.

    How to Create VBA UserForm - Step 6

    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.

  7. Print the values in a message box and define Name, Age, and Gender in different lines using the vbCrLf function.

    How to Create VBA UserForm - Step 7


    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

  8. Run the VBA code. It will run the UserForm.

    How to Create VBA UserForm - Step 8a

    Enter the details and click “Submit.”

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.

Formatting VBA UserForm - 1

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.

Formatting VBA UserForm - 2

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

Formatting VBA UserForm - 3

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

Formatting VBA UserForm - 4
Formatting VBA UserForm - 5

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.

Formatting VBA UserForm - 6

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

Formatting VBA UserForm - 7

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


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.
 Example 1 - Step 1
  • Step 2: Add the necessary elements. We’re creating a VBA UserForm label and a VBA UserForm TextBox.
Example 1 - Step 2a
Example 1 - Step 2b
  • Step 3: Customize the textboxes and Label names by editing the Properties section of the UserForm and its elements.
Example 1 - Step 3a

You will get the following result.

Example 1 - Step 3b

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.
Example 1 - Step 4
  • Step 5: Initialize two string values to record the correct username and password.
Example 1 - Step 5
  • Step 6: Initialize another set of strings to accept the values given by the user.
Example 1 - Step 6

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.
Example 1 - Step 7a

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

Example 1 - Step 7b

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

Example 1 - Step 7c


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”
MsgBox “Login failed. Incorrect username or password.”, vbExclamation, “Login Status”
End If
End Sub

  • Step 8: Run the userform.
Example 1 - Step 8a

After clicking the “Submit” button we get

Example 1 - Step 8b

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.

Example 2 - Data

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.
Example 2 - Step 1
  • Step 2: Add the necessary elements. Here, we’re creating a VBA UserForm label, a VBA UserForm ListBox, and a VBA UserForm TextBox.
Example 2 - Step 2a
 Example 2 - Step 2b
Example 2 - Step 2c

The resulting UserForm will be as follows:

Example 2 - Step 2d
  • Step 3: By going to the Properties tab, format the UserForm per your preferences.
Example 2 - Step 3

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.
Example 2 - Step 4
  • Step 5: Double-click the UserForm to open the Editor and implement the search function.
Example 2 - Step 5
  • Step 6: Define what you want to be displayed in the Listbox.
Example 2 - Step 6
  •  Step 7: Define the TextBox function, which will call the Search subroutine when text is input.
Example 2 - Step 7
  • Step 8: Initialize a subroutine PerformSearch to search a given table in Excel.
Example 2 - Step 8
  • Step 9: Clear any previous ListBox values and define a range value to hold in the size of the table.
Example 2 - Step 9
  • 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.
Example 2 - Step 10
  • Step 11: Initialize a For loop to search the string value in the textbox input.
Example 2 - Step 11


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.”   


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)
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.
Example 2 - Step 12

VBA Code

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


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.

VBA Code Example - 1

The VBA Code can be edited as such:

VBA Code Example - 2

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:

VBA Code Example - 3
VBA Code Example - 4
VBA Code Example - 5

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


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.

This has been a guide to VBA UserForm. Here we learn how to create, format UserForm in Excel VBA using Properties window & VBA Code, along with examples. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *