How to Create Excel VBA UserForm (with Detailed Steps)

If you are searching for some special tricks to create VBA Userform in Excel then you have landed in the right place. There are some quick steps to create VBA Userform in Excel. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.


Download Practice Workbook

You can download the practice workbook from here:


What Is a UserForm Excel VBA?

UserForm is a pop-up window with a custom interface created by Excel users. To take data input in a very user-friendly design, we can create UserForm in Excel. We can easily create an UserForm in Excel from the visual basic menu in the developer section. Then, use VBA code to take input with the UserForm and put the data into the worksheet.

VBA UserForm Examples:

Using Excel VBA UserForms, you can do many things like inputting student information data and calculating BMI. Attendance calculation, and so on. I am showing here 2 examples and will share those with you in the workbook.

Note:

You will get the respective codes in the attached Excel file.

Example 1: BMI Calculator

BMI calculation is a very easy task and you can create a BMI calculation software with Excel VBA code easily. Here, I have created an interactive UserFform through which you can give input on age, height, and weight very easily and will get the BMI value in a moment.

BMI Calculator in Excel VBA UserForm

Example 2: Student/ Employee Information

To insert employee or student or any information which is usually of large quantity, we can use Excel UserForm. Here, I have created an Excel UserForm, through which you can take information from the students and those data will be inserted into assigned columns automatically. It will help you to create a large dataset avoiding any errors.

Excel VBA Userform


Steps to Create UserForm in Excel Using VBA

In this section, I will show you the quick and easy steps to create VBA Userform, initialize UserForm, to open and close UserForm. link UserForm input data with the worksheet in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

In this article, we will create a UserForm to take student information as input and store the data in a worksheet. The UserForm will be as shown below.


Step 1: Create a Blank UserForm

  • For this, first, go to the top ribbon and press on the Developer, and then press on the Visual Basic option from the menu.
  • You can use ALT + F11 to open the “Microsoft Visual Basic for Applications” window if you don’t have the Developer tab added.

Open Visual basic

  • Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “UserForm’” option.

Insert Userform

  • As a result, there will create a blank UserForm with the title “UserForm1”. Now, we will make a custom interface as our need. There you will find another pop-up window titled Toolbox. This is the place from where you will create all the buttons, lists, boxes, etc.

UserForm in Excel

  • If somehow, the UserForm window closes then, double click on the VBAProject >> Forms >> UserForms1 The window will appear again.

Find Userform

  • If the toolbox window disappears then, go to the View tab and click on the Toolbox Then, the Toolbox window will appear again.

Open Toolbox of Userform


Step 2: Resize the UserForm

First, you have to resize the user form as per your need. Click on the right-bottom corner with the mouse cursor and drag it to resize.

Resize the UserForm


Step 3: Create a Label

Now, we are starting to create the custom interface of the UserForm. There are two portions in a UserForm. The first one is the name or title of the box which is called Label in the UserForm and the second part is the boxes where it will take the user input.

  • To create a Label, click on the Label icon in the Toolbox.
  • Then, draw a box shape in the UserForm with mouse clicking.
  • Thus, a Label box will create. Now, you have to modify

Create a Label in the UserForm

  • To modify, click on the Label Then, you will see a window named “Property” will be opened in the left side. If, in any case, you won’t find that then, rightclick on the Label and select the “Property” option.
  • In the Property window, Change the Label name in the Name This name is very important because in the VBA code you have to use this name to call it.
  • Select the BorderStyle = 1 to create a border around the box.
  • Write “Student ID” in the Caption Caption is that which you want to show as the label.
  • Change the TextAlign = 2 which is the center

Change properties of Labels in Userform


Step 4: Create Necessary Text Boxes

Now, to take the Text input from the user, you have to create a Text box. For this:

  • Similarly for the label box, you have to select the Text Box
  • Then, draw a rectangle in the Userform to create a text box in the suitable position.

Create Text Box in the UserForm

  • After creating the text box, go to the Property window. Change the name to “SIDbox”.

Step 5: Create Yes and No Button in UserForm Frame

Now, to take the Yes/No answer from the user you can add a yes and no button which is called Option Button in the ToolBox. But, before inserting, the Options button, you must create a Frame. Because without adding a frame, you can add the yes/no button more than once. If you multiple Yes/No buttons without a frame then they all will be connected to each other. To create a frame in the UserForm:

  • Select the Frame icon in the Toolbox and draw a box in the UserForm.
  • Then, go to the Properties and give a name and a caption for the frame.

Create Yes and No Button in a Frame of UserForm

  • Now, create two Option Buttons inside the frame for Yes and No.
  • After creating the Option Buttons, go to the properties and give the proper Name and Caption.


Step 6: Create Drop-down Menu with Combo Box

To take the Date input, you can create a drop-down list using Combo Box. For date input, you have to create 3 combo boxes for the day, month, and year. And in this UserForm, I have created another combo box drop-down menu for selecting the Level of Education.

Create Drop-down Menu with Combo Box in UserForm


Step 7: Create a ListBox

Similarly, you can also create a list box in the UserForm. The difference between the combo box and the list box is that in the combo box, you have to select only one option but in the List box, you can select multiple options.

Create ListBox in Excel UserForm


Step 8: Create Command Button for Submit, Clear, and Cancel Commands

To create submit, cancel, or clear buttons you have to create command buttons. Follow the same methods mentioned before also for creating the Command Buttons.Create Command Button for Submit, Clear, and Cancel in UserForm


Step 9: Give Names and Captions of Boxes in UserForm

After creating the labels, text boxes, list boxes, combo boxes, or others you must give names and captions to them.

  • Name: It is used to call the boxes of the UserForm in the VBA code. So, it is very much necessary to give a proper name.
  • Caption: It is the text that is shown in the UserForm. For the Labels, Option Buttons, Command Buttons, or frames, captions are the display text on the UserForm. 

In this UserForm, I have given names and captions as shown in the table below. The VBA code is created according to these names.

Control Name Caption
Label SID  Student ID 
Label FirstName First Name 
Label  MidName Middle Name 
Label LastName Last Name 
Label ParentName  Parent Name 
Label ParentName  Contact Number 
Label dateofbirth Date of Birth 
Label  tLevel Level of Education 
Label RegStuden  Regular Student? 
Label mail Email ID 
Label foreignstudent Foreign Student? 
TextBox  SIDbox   –
TextBox fnamebox   –
TextBox mnamebox  –
TextBox lnamebox  –
TextBox pnamebox  –
TextBox contactbox   –
TextBox mailbox   –
ComboBox  day   –
ComboBox  month   –
ComboBox year  –
ComboBox edulevel   –
OptionButton  Regularyes   Yes
OptionButton Regularno  No
OptionButton ForeignYes  Yes
OptionButton ForeignNo  No
Frame  Regular  Regular 
Frame  foreign  Foreign
CommandButton  SubmitCommand  Submit
CommandButton ClearCommand  Clear
CommandButton CancelCommand Cancel
  • Thus, you have created a UserForm. But, it’s not ready yet to take data entry from users. You have to write VBA code to make it usable.

Excel VBA UserForm


Step 10: Insert VBA Code to Initialize the UserForm

At first, you have to initialize the code. Initialization means to make the UserForm ready to take the user input. So, it has to remove the previous inputs. And, for the list box, and combo boxes you have to assign the items to create drop-down lists. For this:

  • Double-click on the UserForm to insert the VBA code. Or you can right-click on the UserForm and select the View Code

View code of Userform

  • Now, in the blank module paste the VBA Code given below:
Private Sub UserForm_Initialize()
SIDbox.Value = ""
fnamebox.Value = ""
mnamebox.Value = ""
lnamebox.Value = ""
mailbox = " "
pnamebox = " "
day.Clear
month.Clear
year.Clear
With day
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With
With month
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With
With year
.AddItem "2017"
.AddItem "2018"
.AddItem "2019"
.AddItem "2020"
.AddItem "2021"
.AddItem "2022"
End With
edulevel.Clear
With edulevel
.AddItem "Level 5"
.AddItem "Level 6"
.AddItem "Level 7"
.AddItem "Level 8"
.AddItem "A Level"
.AddItem "O Level"
End With
ForeignNo.Value = True
Regularno.Value = True
SIDbox.SetFocus
End Sub

🔎 VBA Code Explanation:

Private Sub UserForm_Initialize()
  • Created a Private Sub to initialize the UserForm.
SIDbox.Value = ""
fnamebox.Value = ""
mnamebox.Value = ""
lnamebox.Value = ""
mailbox = " "
pnamebox = " "
  • Making the text boxes blank to take new user input.
day.Clear
month.Clear
year.Clear
edulevel.Clear
  • Clearing the Combo boxes to take new user input.
With day
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With
  • Assigning items to the drop-down list of the day value of date. It can be from 1 to 31.
With month
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With
  • Assigning items to the drop-down list of the month value of date. It can be from January to December.
With year
.AddItem "2017"
.AddItem "2018"
.AddItem "2019"
.AddItem "2020"
.AddItem "2021"
.AddItem "2022"
End With
  • Assigning items to the drop-down list of the year value of date. I have listed the years from 2017 to 2022 in the list.
With edulevel
.AddItem "Level 5"
.AddItem "Level 6"
.AddItem "Level 7"
.AddItem "Level 8"
.AddItem "A Level"
.AddItem "O Level"
End With
  • Assigning the available levels offered by the institutions in the Education Level drop-down menu.
ForeignNo.Value = True
  • Setting the option No selected for the Foreign Student
Regularno.Value = True
  • Setting the option No selected for the Regular Student
SIDbox.SetFocus
  • Setting focus on the value of student id.
End Sub
  • Thus, the sub will end.

Step 11: Insert VBA Code for Putting UserForm Data into WorkSheet

After taking the user input, you have to store the data in a worksheet. For this, paste this VBA code in the Module:

Private Sub SubmitCommand_Click()
Dim emptyRow As Long
Sheet1.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 1).Value = SIDbox.Value
Cells(emptyRow, 2).Value = fnamebox.Value
Cells(emptyRow, 3).Value = mnamebox.Value
Cells(emptyRow, 4).Value = lnamebox.Value
Cells(emptyRow, 5).Value = day.Value & "/" & month.Value & "/" & year.Value
Cells(emptyRow, 6).Value = pnamebox.Value
If ForeignYes.Value = True Then
Cells(emptyRow, 7).Value = "Yes"
Else
Cells(emptyRow, 7).Value = "No"
End If
Cells(emptyRow, 8).Value = edulevel.Value
If Regularyes.Value = True Then
Cells(emptyRow, 9).Value = "Yes"
Else
Cells(emptyRow, 9).Value = "No"
End If
Cells(emptyRow, 10).Value = contactbox.Value
Cells(emptyRow, 11).Value = mailbox.Value
End Sub

🔎 VBA Code Explanation:

Private Sub SubmitCommand_Click()
  • Creating a new Private Sub which will come to action when the Submit button is pressed.
 Dim emptyRow As Long
  • Creating a new variable of Long
 Sheet1.Activate
  • Activating the Sheet1 so, the aftward commands will be effective for the sheet1 of the workbook.
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  • This code finds out the first empty row of the worksheet so, there can be stored the data from the UserForm.
Cells(emptyRow, 1).Value = SIDbox.Value
Cells(emptyRow, 2).Value = fnamebox.Value
Cells(emptyRow, 3).Value = mnamebox.Value
Cells(emptyRow, 4).Value = lnamebox.Value
Cells(emptyRow, 5).Value = day.Value & "/" & month.Value & "/" & year.Value
Cells(emptyRow, 6).Value = pnamebox.Value
  • Assigning Student Id in column A, First Name in column B, Middle Name in Column C, Last Name in column D, and so on.
If ForeignYes.Value = True Then
Cells(emptyRow, 7).Value = "Yes"
Else
Cells(emptyRow, 7).Value = "No"
End If
  • If the user selects the Yes option in Foreign frame then the code will insert “Yes” in column G of the active row else will insert “No”.
Cells(emptyRow, 8).Value = edulevel.Value
  • Inserting the level of education in column H of the active row.
If Regularyes.Value = True Then
Cells(emptyRow, 9).Value = "Yes"
Else
Cells(emptyRow, 9).Value = "No"
End If
  • If the user selects the Yes option in the Regular frame then the code will insert “Yes” in column I of the active row else will insert “No”.
 Cells(emptyRow, 10).Value = contactbox.Value
 Cells(emptyRow, 11).Value = mailbox.Value
 End Sub
  • Inserting the Contact Number and Mail Id in columns J and K respectively and ending the sub.


Step 12: Add VBA Code to Close UserForm Window

For closing or canceling the UserForm window, you have to insert this VBA code into the module. This code will come into action when the user clicks on the Cancel button.

Private Sub CancelCommand_Click()
Unload Me
End Sub

Step 13: Add VBA Code to Clear UserForm

After taking one input, if you want to insert another input in the UserForm then you have to clear the previous input first and initialize the UserForm. To clear the UserForm, you will click on the Clear button and the following VBA code will come into action. Add this code with the previous code in the same module.

Private Sub ClearCommand_Click()
Call UserForm_Initialize
End Sub

Step 14: Add VBA Code to Create a Command Button to Open UserForm

You can easily run the code and open the UserForm by pressing on the Run option, and here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code. But, this is a clumsy and slow process. So, to make it more user-friendly, you can create a command button in the Excel worksheet. For this, follow the steps below:

  • First, go to the Developer tab in the top ribbon.
  • Click on the Insert option and select the Command Button under the ActiveX control

Create a Command Button

  • Then, draw a rectangle box in a suitable region to create the command button.

draw a box for Command Button

  • Then, rightclick on the button and select the View Code

View code for Command Button

  • Then, in the module, paste the following code.
Private Sub CommandButton1_Click()
StudentInformation.Show
End Sub

Code of Command Button

  • Now, to rename the code, rightclick on the button and go to the Properties
  • And, remove the text in the Caption box and give a suitable name for the button.

rename command button

Now, your Excel VBA UserForm is ready to use.


Last Step: Test the Userform

Now, it’s time to test the UserForm. For this, click on the command button in the worksheet. As a result, the UserForm window will appear.

Test the Excel VBA Userform

  • Give the required data in the blank boxes of the UserForm and press the Submit
  • Thus, you will see the data inputted in the UserForm are stored in the assigned columns in the
  • So, the UserForm is working completely fine.

output of Excel VBA Userform


Conclusion

In this article, you have found how to create Excel VBA Userform, initialize, store input data in the worksheet, and cancel or clear UserForm. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo