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.


Download Excel Workbook

Download the Dataset to use it as a Template.


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.


2 Easy Excel VBA Userform Examples to Work With

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.

Employee Information

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

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

Data Entry-Excel VBA Userform Examples

⧭Note: Make sure the workbook contains only one (01) worksheet.

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.

BMI-Excel VBA Userform Examples

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

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

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

BMI Calculator-Excel VBA Userform Examples


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.

Calling Excel VBA Userforms


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

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.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.

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

Tags:

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment

Leave a reply

ExcelDemy
Logo