How to Use an Excel VBA Userform – 2 Examples

This is an overview.


Creating Your Own UserForm

You can develop a macro that works with different data in different circumstances, using 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 it.  VBA’s built-in StrConv function is used here.

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

The macro can be improved:  it can also change the cells to lowercase or proper case (only the first letter of each word is uppercase) and display a dialog box to ask the user what type of case to use.

Using this link, you can create a userform that will change cases to Upper, Lower or Proper.

 


Example 1 – Using a VBA Userform to Enter Data

  • Prepare a Userform. Choose a layout.

Employee Information

1.1 Names and Captions in the Property Window

  • Multiple Controls were inserted using the Toolbox assigned to the Userform. The controls 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 Assigned Macros

In the Userform, two macros were assigned to two Command Buttons.

Macro Assigned to the 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 Breakdown

  • eName.Value is the Text Value in the “Employee Name” entry in Userform.
  • eID.Value is the Text Value in the “Employee ID” entry in Userform.
  • eDept.Value is the Text Value in the “Department” entry in 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 and adds a new row.
 mData = Cells(Rows.Count, 1).End(xlUp).Row + 1

Macro Assigned to the Cancel Button

Private Sub CancelData_Click()
EmployeeInformation.Hide
End Sub

Macro Breakdown

  • EmployeeInformation.Hide hides the Userform upon clicking the Cancel Button.
EmployeeInformation.Hide

1.3 The Outcome of the Userform

After assigning the macros, run the userform to display the userform window.

  • Press F5 to display the userform.
  • Enter data and click Submit in the userform. The entered data gets stored in the Worksheet.
  • Click Cancel to exit the userform.

Data Entry-Excel VBA Userform Examples

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

Example 2 – Finding the BMI Using an Excel VBA Userform

Calculate the Body Mass Index using an Excel VBA Userform.

  • Create a Userform maintaining the layout.

BMI-Excel VBA Userform Examples

2.1 Names and Captions in the Property Window

  • The controls 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  Assigned Macros

Two macros were assigned to the Submit and Cancel buttons.

Macro Assigned to the 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 Breakdown

  • 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 Metric Units (KG-Meter)
bmiFind = (bmiWeight) / (bmiHeight) ^ 2
  • For US Customary Units (Ib-Inch)
 bmiFind = (bmiWeight) / (bmiHeight ^ 2) * 703.0704

Macro Assigned to the Cancel Button

Private Sub CloseTheUserform_Click()
Unload BMICalculator
End Sub

Macro Breakdown

  • Unload BMICalculator exits the Userform upon clicking the Cancel Button.
Unload BMICalculator

2.3 The Outcome of the Userform

Run the assigned userform to display the userform window.

  • Press F5 to display the userform.
  • Enter data and click Submit in the userform.
  • The Userform displays the BMI.
  • Click Cancel to exit the userform.

BMI Calculator-Excel VBA Userform Examples


Calling Userforms by a Click in an Excel Worksheet

Insert a Macro Button in the worksheet.

Calling Excel VBA Userforms


 Assigned Macro

Click the Macro link below to create a Macro Button.

  • Right-click> Select Assign Macro on the Macro Button. The Userform will be displayed.
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 Template.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

1 Comment

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo