Tutorial Playlist

Excel Tutorial

Overview

Your One-Stop Solution to Know About Excel Worksheets

Lesson - 1

Your One-Stop Solution For Excel UserForms

Lesson - 2

The Best Guide on How to Send an Email in Excel

Lesson - 3

Your One-Stop Solution That Will Help You Learn How to Convert PDF to Excel

Lesson - 4

A One-Stop Solution for Acing the Excel MIS Report

Lesson - 5

All You Need to Know About Barcode in Excel

Lesson - 6

Your One-Stop Solution to Learn Mail-Merge in Excel and Its Implementation

Lesson - 7

A Comprehensive Guide on Excel Flash Fill

Lesson - 8

Slicers in Excel: The Ultimate Guide to Help You Design and Develop Excel Slicers

Lesson - 9

One-Stop Solution to Learn Everything About Excel Budget Template

Lesson - 10

All You Need to Learn About Match Function in Excel and Its Real-Time Implementation

Lesson - 11

Everything You Need to Learn on How to Create a Pivot Table From Multiple Sheets

Lesson - 12

The Major Fundamentals About XLookUp in Excel Along With Practical Examples

Lesson - 13

The Ultimate Guide to Learn How to Compare Two Columns in Excel

Lesson - 14

The Best Guide and Your One-Stop Solution to Master the Fill Series in Excel

Lesson - 15

What Is the DATE Function in Excel and How to Implement It?

Lesson - 16

Your One-Stop Solution to Designing and Implementing Hyperlinks in Excel

Lesson - 17

One-Stop Solution to Master Everything You Need to Know About Auto-Sum in Excel

Lesson - 18

DAX in Excel: The Complete Guide for DAX Functions and Formulas in Excel

Lesson - 19

A Perfect Guide for All You Need to Know About Data Formatting in Excel

Lesson - 20

The Perfect Guide to Learn How to Add Columns in Excel

Lesson - 21

The Perfect Guide That Will Explain to You How to Remove Page Break in Excel

Lesson - 22

The Perfect Guide to Explain to You How to Remove Blank Rows in Excel

Lesson - 23

The Perfect Guide to Learning How to Highlight Duplicates in Excel

Lesson - 24

One-Stop Solution Understand the Fundamentals of Indirect Function in Excel

Lesson - 25
Your One-Stop Solution For Excel UserForms

Excel User Forms are automated interactive graphical user interfaces that are proven to be handy while storing and manipulating complex organized data.

What Are Excel User Forms?

The Excel User Forms are customized user interfaces that can be designed and developed within Excel VBA. The Excel User Forms enable interaction between the excel spreadsheet or the database via a graphical user interface and helps in data storage, manipulation, and retrieval.

Following this brief introduction of Excel user forms, you will now look into how to create the excel user forms in real-time.

FREE Business Analytics With Excel Course

Start your Business Analytics Learning for FREEStart Learning
FREE Business Analytics With Excel Course

How to Create an Excel User Form?

It is possible to create the Excel user forms using the Excel VBA. Hence, it is necessary to enable the Excel Developer tools.

To enable developer tools, navigate to the File menu in excel. Select the options button in the bottom left corner.

Excel-User-Forms-file-menu.

This will navigate you to the Excel Options. Here, choose the customize ribbon option and click on the check box of the developer option as shown below.

Excel-User-Forms-Excel-Options-Developer-menu.

Now you have the developer option enabled on your toolbar in Excel, as explained.

You can see the first option that reads as Visual Basics. This is our VBA. Click on the option, and it will redirect you to Visual Basic for Applications window, as shown below.

Excel-User-Forms-developer-option

Excel-User-Forms-VBA-Window.

Select the insert option and select the user from the option. This will help you with a new user form in Excel, as shown below.

Excel-User-Forms-new-userform

The tiny tray on the right side is a toolbar helps you with a variety of options, such as the following:

  • Text box
  • Label
  • Toggle button
  • Command Button
  • Frame
  • Tab Strip, etc

Excel-User-Forms-user-form.

Another crucial tiny window is the Excel user forms property window. This is where you edit the properties of Excel user forms. The properties include the following:

  • Visual themes 
  • Name of the user form
  • Colour of the labels and icons on the user form, etc.  

Excel-User-Forms-Excel-user-form-properties-option.

Now, to create user forms in Excel, all you need to do is have a design idea and use the options of label, text box, and other available options to build it. You can build your own Excel user forms by selecting the options from the tool tray or dragging and dropping the options onto the user form.

Here, this example has created an Employee User From in Excel that includes the following:

Labels:

  • Employee ID
  • Employee Name
  • Employee Phone

Text Boxes for the Labels as mentioned above:

  • A text box for Employee ID
  • A text box for Employee Name
  • A text box for Employee Phone

Command Buttons:

  • Insert Entry - Button
  • Refresh the User Form - Button

After the creation of the Excel user form, it appears as follows.

Excel-User-Forms-Employee-User-Form.

Now, head back to the Excel sheet and create the columns for the data labels. Here, there are three data labels, namely:

  • Employee ID
  • Employee Name
  • Employee Phone

The three data columns need to be created in the sheet, as shown below.

Excel-User-Forms-Sheet-Data-Form.

After you create the fundamental requirements of an Excel user form, the next major step is to code the Excel User Form in VBA for operations. 

Coding an Excel User Form

There are two significant instances to code in this particular Excel user form. They are the insert button and refresh button.

You can edit the button code by double-clicking on the button. The following code window will pop up, as shown below.

Excel-User-Forms-Code-Insert-Refresh.

In this window, you need to type in your code for the buttons.

The code used in this example is mentioned below. The first code segment is for click button one or Insert button.

Private Sub CommandButton1_Click()

erow = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row

Range("a" & erow + 1) = TextBox1.Value

Range("b" & erow + 1) = TextBox2.Value

Range("c" & erow + 1) = TextBox3.Value

TextBox1.Value = ""

TextBox2.Value = ""

TextBox3.Value = ""

End Sub

The Second Code segment is for click button two or the refresh button.

Private Sub CommandButton1_Click()

erow = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row

Range("a" & erow + 1) = TextBox1.Value

Range("b" & erow + 1) = TextBox2.Value

Range("c" & erow + 1) = TextBox3.Value

TextBox1.Value = ""

TextBox2.Value = ""

TextBox3.Value = ""

End Sub

Private Sub CommandButton2_Click()

TextBox1.Value = ""

TextBox2.Value = ""

TextBox3.Value = ""

End Sub

Once you are done with the coding part, you can choose to run the program using the play option from the toolbar.

Excel-User-Forms-Play-Pause-Stop-menu.

The following user form will appear on your screen, and you can enter the data in the user form, and it will reflect the same on your sheet as shown below.

Excel-User-Forms-Data-Insertion.

The next part is not mandatory but can be helpful as a creative shortcut for your user form in Excel. This is developed using the Macros in Excel.

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

Creating a Macro for Excel User Form

To create a shortcut button for your user form using macros in Excel, you need to follow the steps mentioned below.

Select the Insert option and choose the new module option as shown below. 

Excel-User-Forms-new-Module

A new code editor will be open on your screen, type the following code mentioned below and save it as shown below.

Sub Enter_Employee_Details()

Employee_Form.Show

End Sub

Excel-User-Forms-Code-Macro

Now, get back to your Excel sheet and enter the developer tool window and insert a button as shown below.

Excel-User-Forms-developer-Button-Macro.

It will show a new window on the screen, which asks you to assign a new macro. You have already designed, developed, and declared a Macro by the name "Enter_Employee_Details". Select the macro and press ok.

Excel-User-Forms-assign-macro

Now, edit the button name as per your requirement, and the process is finished. The final button will look as follows.

Excel-User-Forms-Final-Button.

Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today.

Next Steps

"Round Off Formula in Excel" can be your next stop. This tutorial will guide you to perform the round-off operations of the columns with numerical data with excess decimal points. 

Are you interested in more knowledge on Microsoft Excel or looking for online training for Business Analytics?

Then check out the Business Analytics certification course offered by Simplilearn, which is career-oriented training and certification. This training is dedicated to guiding you through the basic concepts of data analysis and statistics that will help you devise insights from available data to present your findings using executive-level dashboards and help you come up with data-driven decision-making.

Have any questions for us? Please feel free to let us know in the comments section below, and our team of experts will be happy to resolve all your queries.

Happy learning!

About the Author

Ravikiran A SRavikiran A S

Ravikiran A S works with Simplilearn as a Research Analyst. He an enthusiastic geek always in the hunt to learn the latest technologies. He is proficient with Java Programming Language, Big Data, and powerful Big Data Frameworks like Apache Hadoop and Apache Spark.

View More

Find Business Analytics with Excel in these cities

  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.