VBA UserForm

Last Updated :

21 Aug, 2024

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Excel VBA Userform

Userform in VBA are customized user-defined forms made to take input from a user in a form format. Although it has different sets of controls to add, such as text boxes, checkboxes labels, etc., to guide a user to input a value and store the value in the worksheet, every part of the UserForm has a unique code with it.

UserForm is an object within the Excel interface. Inside this UserForm, we can create other useful custom dialog boxes to get the data from the user. When working with a Macro file created by your senior or downloaded from the internet, you must have seen a UserForm.

userform

In this article, we will show you how to create a similar UserForm to store the data from the user.

How to Create Userform?

Let us start.

  1. Like how you insert a new module similarly, you need to click on the INSERT button in Visual Basic Editor to insert UserForm.


    VBA userform Example 1

  2. It will also insert the UserForm as soon as you click on this.


    VBA userform Example 1-1

    Before we tell you how to program this, let me show you how to format this UserForm.

Formatting Userform

Selecting the UserForm, press the F4 key to show you the "Properties" window.

Press F4 Key 1-2

Using this "Properties" window, we can format this UserForm. We can name it. We can change the color, border style, etc.

Name here 1-3
Caption Here 1-4

Like this, try other properties to get the sight of the UserForm.

Now, for this UserForm, insert a toolbox.

VBA userform Example 1-5

Now, we will see a "Toolbox" like this.

VBA userform Example 1-6

At this moment, the UserForm is inserted, not programmed. To know how it works, click on the "Run" button. We will see the form on the excel worksheet.

VBA userform Example 1-7

Now, use the toolbox to draw the label.

Label 1-8

Enter the text as "Employee Name" inside the label.

VBA userform Example 1-9

For this label, we can format it by using properties. For example, we have entered the text as "Employee Name:" and can see this in the properties window under "Caption."

Caption 1-10

Insert one more label. You can either click on a toolbox or drag the current label by holding the Ctrl key to insert one more label. You will have a replica of the current label.

VBA userform Example 1-11

Now, we will have the same label.

VBA userform Example 1-12

Change the name to "Employee ID."

VBA userform Example 1-13

Now, insert one more label and name it "Department."

VBA userform Example 1-14

Now, from the toolbox, insert a text box.

VBA userform Example 1-15

Name this text box as Employee Name in the “Properties” window.

VBA userform Example 1-16

Like this, insert two more text boxes from "Employee ID" and "Department," respectively. Name those text boxes as per their heading.

VBA userform Example 1-17

Similarly, please do it for the "Department."

VBA userform Example 1-18

Now, from the toolbox, insert "Command Button."

VBA userform Example 1-19

Change the name of the "CommandButton" to "SubmitButton" and change the caption to "Submit."

VBA userform Example 1-20

Insert one more button and call it "Cancel."

VBA userform Example 1-21

Now, to see run, press the "Run" button or use the F5 key and see how your UserForm looks like in Excel.

VBA userform Example 1-22

Now, it is coming into shape.

VBA Code

Now, the user will enter data, so we need to program this to store the data entered by the user on this form.

Double click on the SUBMIT button, which will take you to the Macro window with an auto-created Macro like the one below.

Submitbuton 1-23

It says, "SubmitButton" click. Remember we had given a name for the SUBMIT button as "SubmitButton."

VBA userform Example 1-24

So, whenever we want to call this button, we can call this by this name (submit button). Inside this Macro, copy and paste the below code.

Code:

Private Sub SubmitButton_Click()
  
  Dim LR As Long

   LR = Cells(Rows.Count, 1).End(xlUp).Row + 1

   Cells(LR, 1).Value = EmpName.Value
   Cells(LR, 2).Value = EmpID.Value
   Cells(LR, 3).Value = Dept.Value

   EmpName.Value = ""
   EmpID.Value = ""
   Dept.Value = ""

End Sub
SubmitButton code 1-25
  • EmpName.Value here EmpName is the text box name we had given while creating the employee name text box.
  • EmpID.Value here EmpID is the text box name of the "Employee ID" text box.
  • Dept.Value this is the "Department" text box name.

So, clicking the "Submit" button will store the values in the mentioned cells.

Now, double-click on the "Cancel" button. It will also show you the auto macro name like this.

Cancel Button 1-26

Please copy the below code and paste it.

Code:

Private Sub CancelButton_Click()

 MyUserForm.Hide

End Sub
Cancel button hide 1-27

MyUserForm is the name we had given to the UserForm. MyUserForm.Hide means clicking the CANCEL button, which will hide the UserForm.

Now, create a template like this in the worksheet.

VBA userform Example 1-28

Delete all the other sheets in the workbook except this template sheet.

Now, go to Visual Basic Editor.

VBE 1-29

And run the Macro using the F5 key or manually. We will see the UserForm in front of us.

VBA userform Example 1-30

Enter the Employee Name, Employee ID, and Department name.

VBA userform Example 1-31

Now, if you click on the SUBMIT button, it will store the values on the template we have created.

VBA userform Example 1-32

Like this, you can keep entering the names. The UserForm will keep storing the values entered by the user in the specified cells.

So, by using the USERFORM, we can create wonderful projects to get information from the user.

Recommended Articles

This article has been a guide to VBA UserForm. Here, we learn how to create an Excel VBA UserForm to store data from the user, along with a practical example and downloadable template. Below you can find some useful Excel VBA articles: -