Getting Started With Excel VBA UserForms

In this article, will learn the basics of VBA userforms. I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topic using one example and step by step guide. If you are reading this, than I assume you know the basics of Excel VBA.
Without further delay, let’s get started.
Create A Form for Investment in Excel and Store Record
We will start by designing the user form. In this userform, we will have one textbox for name,  one for age, one for investment amount and a pair of radio buttons for gender. Let’s start it.

 Design the userform

• Open the visual basic editor in excel using ALT+F11 shortcut. In Project Box, right click on VBAProject(yourfile). In insert option, choose UserForm.



• Immediately, a new folder will be created and your userform will be found their. All userform for this project will be added in this folder.
• Change the name of form to InvestmentForm in the property box.

The property box is shown in the lower left of the project explorer. If you can’t see it, go to view and click on Properties Window.  We will be using it a lot, so make sure it is there. It is used for styling, naming and customisation of forms.


• Add elements to form: In above image I have already added elements (labels, textbox, command button). However a new userform is completely blank.You need to use tool box to add elements to your form. If you can’t see the toolbox, get it from view tab.


Add labels using toolbox for descriptive names.  Add textbox for user inputs. 

Add two radio buttons and name them Male and Female. 

I have used a frame to encapsulate them, but it is not necessary. 

Add command buttons submit and cancel to perform operation on given input.

• Name Elements: The textbox, labels, buttons, etc. all are elements. And in order to use them in VBA code we need to give them names. We use property window to change their name. 
• Select the element. Here I am select the textbox for name. Go to property window and change the name to “NameBox”. Do the same for each element you are going to use. (you don’t need to name labels, unless you want them to be clickables.)

I have renamed the element as below table and I will use these names to refer to them. You can have different names. Just replace these names with your name. These are VBA names (code name) that will be used in code. They will not be reflected on form.
The text you can see on labels and buttons are “Captions”. The name and caption of element can be same, if you want.

• Show User Form to User: Now the form is ready, let’s show it to the user. But wait, how do I do that. There’s no option on worksheet to call user form.

Actually, user form needs trigger. It can not be shown by itself. You can use, a command button, a subroutine or an event to make form pop up on the screen.

Here I am going to use a command button to trigger user form.

• On a worksheet, go to developers tab? Insert? Button (form control). Rename it open form.
• Right click on it. Click on assign macro and then click on new.



• A sub will be created immediately. Now add this line to that sub.

Sub Open_Form()
'Opening form
 InvestmentForm.Show
End Sub

It is done. Go back to that sheet and click on the button. The user form will pop up.

• Fill sheet using VBA user form: As we clicked on command button (open form) the form pops up. Now you can fill the form. But when we click on on submit button, data should have been entered on this sheet, but nothing happens. 

Because we have not written any vba instruction for that. We need to store user form data into sheet. 

• Go back to VBA and double click on the Submit button. A new sub will be create automatically. This sub is embedded in the form and you can’t find it in any module. This is same for each element of form.
• Write this vba code in that sub.

Private Sub SubmitButton_Click()

Sheet1.Activate

'get first empty row on sheet (read about it here)
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
Set firstEmptyRow = Range("A" & lstrow + 1)

'initialize each cell with data
firstEmptyRow.Offset(0, 0).Value = nameBox.Value 'first cell
firstEmptyRow.Offset(0, 1).Value = AgeBox.Value 'first cell to the right
firstEmptyRow.Offset(0, 3).Value = InvestBox.Value 'third cell to the right

'checking radio button
If MaleOption.Value = True Then
    firstEmptyRow.Offset(0, 2).Value = "Male" 'second cell to the right
Else
    firstEmptyRow.Offset(0, 2).Value = "Female" 'second cell to the right
End If

'Closing form
Unload Me

End Sub

The above snippet of VBA code runs when submit button is clicked. It finds the first empty row on the sheet and fills it with values provide in form. And in the end it closes the form by using command “Unload Me”.

• Use Cancel command to close the form. For now, cancel button is useless. It is doing nothing. In case if you want to abort the input, you can use cancel button. In that case:

Double click on the cancel command button in VBA editor. A new sub will be created. Just write this line of code to close the user form.

Private Sub CancelButton_Click()
 'Closing form
  Unload Me
End Sub

Finally the form code will look like this.

And that’s it. This is how you use excel vba userform to get input from users. In this article, we just explored the basic use of userform, so that you get familiar with it. It is simple, if you have knowledge of basic vba.

In future articles, we will explore more advanced functionalities of vba userforms in Excel. We will create userform in excel to do a lot more. We will explore different tools and elements available to vba userform. Till then practice this. You can download this file for reference if you want.
So yeah guys, this was a small and simple userform tutorial. I hope it was resourceful. Let me know if you have any doubts regarding this, let me know in the comments section below.
Related Articles

Change the value/content of several UserForm-controls using VBA in Excel

Prevent a userform from closing when the user clicks the x-button by using VBA in Excel

Return the cells referenced by user using RefEdit control of userform in Excel
Popular Articles:
The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel