VBA UserForm

Excel VBA Userform

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

Userform is an object within the Excel interface, and inside this user form, we can create other useful custom dialogue boxes to get the data from the user. When you are working with a macro file that is created by your senior or may be downloaded from the internet, you must have seen a userform like this.

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?

Lets start.

You can download this VBA Userform Excel Template here – VBA Userform Excel Template

  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. As soon as you click on this, the user form will also be inserted.


    VBA userform Example 1-1

    Before I tell you how to program this, let me show you how to format this userform.

Formatting Userform

By selecting the userform, press the F4 key, it will 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 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 just inserted, not programmed. To know how it works, just click on the run button. We will see the form on the excel worksheet.

VBA userform Example 1-7

Now using the ToolBox draw label.

Label 1-8

Enter the Text as Employee Name inside the label.

VBA userform Example 1-9

For this label, so we can format by using properties. Now we have entered the text as “Employee Name:” now we can see this in the properties window under Captions.

Caption 1-10

Insert one more label. To insert one more label, either you can click on a toolbox, or you can drag the current label by holding the Ctrl key. 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 similarly insert one more label and name it as “Department.”

VBA userform Example 1-14

Now from the toolbox, insert a text box.

VBA userform Example 1-15

Name this text box as EmpName in the properties window.

VBA userform Example 1-16

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

VBA userform Example 1-17

Similarly, 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 Command Button 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 just 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 to the shape.

VBA Code

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

Double click on the SUBMIT button, it will take you to the macro window with an auto-created macro like the 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, on the click on the submit button, it will store the values in the mentioned cells.

Now double click on the Cancel button. This will also show you the auto macro name like this.

Cancel Button 1-26

Copy the below code and paste.

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 on the click on the CANCEL button it will hide the userform.

Ok, 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 user form 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 to 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 USER FORM, we can actually create wonderful projects to get the information from the user.

Recommended Articles

This 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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>