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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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
Now, get back to your Excel sheet and enter the developer tool window and insert a button as shown below.
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.
Now, edit the button name as per your requirement, and the process is finished. The final button will look as follows.
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 for Strategic Decision Making with IIT Roorkee 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!
