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. This tutorial will cover the following topics:

  • What are Excel User Forms?
  • How to Create an Excel User Form?
  • Coding an Excel User Form
  • Creating a Macro for Excel User Form

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.

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program 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
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.