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 SubThis 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.
| Control | Name | Caption |
|---|---|---|
| Label | employeename | Employee Name |
| Label | employeeid | Employee ID |
| Label | department | Department |
| TextBox | eName | – |
| TextBox | eID | – |
| TextBox | eDepartment | – |
| CommandButton | SubmitData | Submit |
| CommandButton | CancelData | Cancel |
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
Macro Explanation
- eName.Value is the entered Text Value in the “Employee Name” entry on Userform.
- eID.Value is the entered Text Value in the “Employee ID” entry on Userform.
- eDept.Value is the entered Text Value in the “Department” entry on Userform.
- The VBA Cells property enters the provided entries in different columns of the same row.
Cells(mData, 1).Value = eName.Value
Cells(mData, 2).Value = eID.Value
Cells(mData, 3).Value = eDept.Value- The mData variable counts the existing rows then adds a new row to itself.
mData = Cells(Rows.Count, 1).End(xlUp).Row + 1Macro Assigned to Cancel Button
Private Sub CancelData_Click()
EmployeeInformation.Hide
End Sub 
Macro Explanation
- EmployeeInformation.Hide command hides the Userform upon clicking the Cancel Button.
EmployeeInformation.Hide1.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.
| Control | Name | Caption |
|---|---|---|
| OptionButton | MetricUnits | Metric Units |
| OptionButton | USCustomaryUnits | US Customary Units |
| Label | EnterTheWeight | Enter The Weight |
| Label | EnterTheHeight | Enter The Height |
| TextBox | mWeight | – |
| TextBox | mHeight | – |
| TextBox | mBMI | – |
| CommandButton | FindTheBMI | Find The BMI |
| CommandButton | CloseTheUserform | Close The Userform |
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
Macro Explanation
- The Excel CDBL function converts the provided values to Double Data.
Let bmiWeight = CDbl(mWeight.Value)
Let bmiHeight = CDbl(mHeight.Value)- The formulas find the BMIs for respective units.
- For Metric Units (KG-Meter)
bmiFind = (bmiWeight) / (bmiHeight) ^ 2- For US Customary Units (Ib-Inch)
bmiFind = (bmiWeight) / (bmiHeight ^ 2) * 703.0704Macro Assigned to Cancel Button
Private Sub CloseTheUserform_Click()
Unload BMICalculator
End Sub
Macro Explanation
- Unload BMICalculator exits the Userform upon clicking the Cancel Button.
Unload BMICalculator2.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.
