How to Use Excel VBA Userform (2 Suitable Examples)

It is impossible to use Excel for a long time without being exposed to dialog boxes. Excel uses dialog boxes to get information from the users, make commands understandable to the users, and display messages to the users. Using VBA macros, we can create our own dialog boxes that work like the built one dialog boxes in Excel. Our created dialog boxes are called UserForms. And in this article, we discuss some Excel VBA Userform examples.

The Reason Behind Creating Your Own UserForm

For example, you have an Excel worksheet. You have some sales data recorded on the worksheet. You have developed a macro. This macro uses the above sales data and calculates something using these data as per your requirement and then shows the results in another portion of your Excel sheet. So, this macro is developed to do the same thing every time.

You can develop the above macro in such a way that it will work with different data in different circumstances. In such cases, the macro may use custom dialog boxes (UserForm).

The following macro changes the text of a selected cell range to uppercase. But if the cell range has a formula, the macro does not change anything. The procedure uses VBA’s built-in StrConv function.

Sub ChangeCase()
    For Each cell In Selection
        If Not cell.HasFormula Then
        cell.Value = StrConv (cell.Value, vbUpperCase)
        End If
    Next cell
End Sub

This macro is useful, but you can improve it. For example, the macro would be more useful if it could also change the cells to lowercase or proper case (only the first letter of each word is uppercase).

Like in the following figure, you can make a dialog box to ask the user to know what type of case change she/he wants. You can create this type of custom dialog box using UserForm in the Visual Basic (VB) Editor. The created UserForm is displayed by a VBA macro. Using this link, you can create a userform that will change cases to Upper, Lower or Proper.

However, there is another solution in Excel VBA. You can create three macros. Three macros will handle three types of case changes. But this is a toilsome and inefficient way.

How to Use Excel VBA Userform (2 Suitable Examples)

Excel VBA Userforms have a plethora of usage benefits for Excel users. In this article, we demonstrate some common examples of Userform.

It’s basic to create a userform layout in Excel VBA. Go through This Article to learn the detailed step-by-step procedure to create one.

Example 1: Using VBA Userform to Make Data Entry Easier in Excel

As it’s discussed in the linked article on how to create a userform, we are not repeating the process in this article.

• Prepare a Userform with a preferred layout.

1.1 Names and Captions in the Property Window

• We inserted multiple Controls using the Toolbox assigned for Userform. The controls names, their names, and captions are listed below.

1.2 Respective Assigned Macros

In the Userform, we assign two (02) macros to two (02) Command Buttons.

Macro Assigned to Submit Button 

Private Sub SubmitData_Click()
   Dim mData As Long
   mData = Cells(Rows.Count, 1).End(xlUp).Row + 1
   Cells(mData, 1).Value = eName.Value
   Cells(mData, 2).Value = eID.Value
   Cells(mData, 3).Value = eDept.Value
   eName.Value = ""
   eID.Value = ""
   eDept.Value = ""
End Sub

 Cells(mData, 1).Value = eName.Value
 Cells(mData, 2).Value = eID.Value
 Cells(mData, 3).Value = eDept.Value

 mData = Cells(Rows.Count, 1).End(xlUp).Row + 1

Macro Assigned to Cancel Button

Private Sub CancelData_Click()
EmployeeInformation.Hide
End Sub

EmployeeInformation.Hide

1.3 The Outcome of the Userform

After assigning the macros, users have to run the userform to fetch the userform window.

• Press F5 to display the userform. Enter respective entries then hit Submit on the userform. The entered data gets stored in the Worksheet. Click on Cancel to exit from the userform.

Example 2: Finding BMI Using Excel VBA Userform

In this example, we demonstrate Body Mass Index calculation using an Excel VBA Userform.

• Create a Userform maintaining a layout as shown in the below picture.

2.1 Names and Captions in the Property Window

• The controls names, their names, and captions are listed below.

2.2 Respective Assigned Macros

Similar to the previous example, we assign two (02) macros to the Submit and Cancel buttons.

Macro Assigned to Submit Button 

Private Sub FindTheBMI_Click()
Dim bmiWeight As Double
Dim bmiHeight As Double
Dim bmiFind As Double
 If MetricUnits.Value = True Then
 Let bmiWeight = CDbl(mWeight.Value)
 Let bmiHeight = CDbl(mHeight.Value)
 bmiFind = (bmiWeight) / (bmiHeight) ^ 2
 bmiFind = Format(bmiFind, "0.00")
 Let mBMI.Value = bmiFind
 End If
 If USCustomaryUnits.Value = True Then
 Let bmiWeight = CDbl(mWeight.Value)
 Let bmiHeight = CDbl(mHeight.Value)
 bmiFind = (bmiWeight) / (bmiHeight ^ 2) * 703.0704
 bmiFind = Format(bmiFind, "0.00")
 Let mBMI.Value = bmiFind
 End If
End Sub

 Let bmiWeight = CDbl(mWeight.Value)
 Let bmiHeight = CDbl(mHeight.Value)

bmiFind = (bmiWeight) / (bmiHeight) ^ 2

 bmiFind = (bmiWeight) / (bmiHeight ^ 2) * 703.0704

Macro Assigned to Cancel Button

Private Sub CloseTheUserform_Click()
Unload BMICalculator
End Sub

Unload BMICalculator

2.3 The Outcome of the Userform

Users have to run the assigned userform to fetch the userform window.

• Use the F5 key to display the userform. After providing the entries, hit Submit on the userform. The Userform displays the BMI Click Cancel to exit from the userform.

Calling Userforms by a Click on Excel Worksheet

To call the userform without pressing the F5 key, users can insert a Macro Button in the worksheet. Visit This Link to be able to create a Macro Button.

Respective Assigned Macro

• Right-click> Select Assign Macro on the inserted Macro Button to assign a macro. Upon clicking the macro button, Excel will fetch the respective Userform.

Private Sub CancelData_Click()
EmployeeInformation.Show
'Or EmployeeInformation.Show
End Sub

Read More: How to Create a UserForm: an Overview

Download Excel Workbook

Download the Dataset to use it as a Template.

Conclusion

This article discusses the importance of the Excel VBA Userform and its examples. We hope these examples encourage you to use userforms more often in your day-to-day usage. Comment if you have further inquiries or have anything to add.

This article is part of my series: VBA & Macros in Excel – A Step by Step Complete Guide and Make UserForms in VBA Excel with Total 6 Tutorials.

Related Articles

• How to Make a Calculator in Excel
• How to Create a Calculator Using Macros in Excel
• Excel VBA: Create a Progress Bar While Macro Is Running
• How to Create Cascading Combo Boxes in Excel VBA User Form