Khan Muhammad Kawser
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.
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.
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.
- 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.
- 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.
- If somehow, the UserForm window closes then, double click on the VBAProject >> Forms >> UserForms1 The window will appear again.
- If the toolbox window disappears then, go to the View tab and click on the Toolbox Then, the Toolbox window will appear again.
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.
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
- 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, right–click 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
Read More: [Fixed!] Border Not Showing in Excel (6 Solutions)
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.
- After creating the text box, go to the Property window. Change the name to “SIDbox”.
Read More: How to Use VBA Input Function in Excel (2 Examples)
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.
- 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.
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.
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.
Similar Readings
- How Different Is VBA from Other Programming Languages
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- 22 Macro Examples in Excel VBA
- 20 Practical Coding Tips to Master Excel VBA
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 | 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.
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
- 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.
Read More: How to Use VBA Modules in Excel (8 Simple Ways)
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 SubStep 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 SubStep 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
- Then, draw a rectangle box in a suitable region to create the command button.
- Then, right–click on the button and select the View Code
- Then, in the module, paste the following code.
Private Sub CommandButton1_Click()
StudentInformation.Show
End Sub
- Now, to rename the code, right–click on the button and go to the Properties
- And, remove the text in the Caption box and give a suitable name for the 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.
- 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.
Read More: How to Use Excel VBA Userform (2 Suitable Examples)
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.
