Excel forms, or Userforms, can be used to collect information from a user, validate it, then enter that data into your worksheet.
In this post I am going to show you how to create a form in Excel that gathers data, then populates a table for tracking expenses.
This is meant as a simple example and you can adapt the process to many other tasks.
Related Articles: I've written other posts on Excel Forms related to this which you might find useful.
Excel Date Picker (Calendar) Using a VBA class to implement a date picker. This is a more robust solution than using an ActiveX control and more easily distributed to other people.
Excel Form Data Validation How to check the data being entered in your form is correct, and communicating with the user about errors
Excel Forms : Insert, Update and Delete Data Using your Excel Form to insert, update and delete records in a table.
Creating Excel Forms - A Video
Watch this video to see how I build the form, and the VBA code that makes it work. I cover some things in more detail than I can just writing about it and if you are a visual learner it will be invaluable to you.
Creating Excel Forms - The Steps Involved
You can break down the steps involved in creating a form thus:
- Designing the form
- Building the form
- Writing the VBA code
Designing the form
Before you rush off to create your awesome new Excel forms, you first need to know what it is you want the userform to do. Let's look at our sheet and see what data we will be gathering. The workbook I am using is a modified version of a template provided by Microsoft. Here's a screenshot:
We need to gather a date, staff and client names, a description, several cash values, and a Yes/No regarding whether or not receipts were supplied. This data is in tabular format and the totals are worked out for us. Read about preparing data in tabular format if you aren't sure how to set this up.
I find that making a sketch on paper helps me to get a rough layout for the way I want the form to look. For small, simple userforms with only a couple of fields, this first step isn't always necessary, but for larger, more complicated forms, it can save you a lot of time.
Building the form
Forms are created through the VBA editor, press ALT+F11 in Excel to open it.
On the left hand side in the Project Explorer we see the workbook (our project) and the sheets in the workbook.
The userform and its code are stored in userform modules so let's insert one of these. Right click on the project -> Insert -> Userform.
You get a new Userform module and a blank userform.
We use the Toolbox to insert the controls we use in the form. The term 'controls' refers to both the inputs we can use like text boxes, option buttons, combo boxes etc., and things like labels and frames.
Let's start by inserting a date picker/calendar. The date picker or MonthView control to give it it's proper name, isn't available by default. To be able to use it you must first right click on the Toolbox below the existing controls, and then click Additional controls.
PLEASE NOTE: Unfortunately this control only works with 32 bit versions of Excel.
An excellent alternative for both 32 bit and 64 bit Excel forms is to use this Excel Date Picker (Calendar), which is implemented as VBA class and easily distributed to other people. It was created by our good friends at E90E50.
If you can't find the MonthView control in your Additional Controls you will need to download it from Microsoft. This is a .cab (Cabinet) file which is like a ZIP file and you will need to extract the contents using your archiver, like WinZip or 7Zip. Then follow these instructions to install it. Anybody you send your workbook too will also need to have the same file installed.
- Copy the extracted files into your System32 folder, often located at C:\Windows\System32. If you're using Windows 7 or above, copy these files into C:\Windows\SysWOW64 folder.
- Open a Command Prompt, in Win7 and above run the Command Prompt as Administrator.
- Windows 7: click on your Start button, type cmd, wait for the cmd.exe file to be found, the right click it and choose 'Run as Administrator'
- Windows 8: Windows Key + X, then click/touch Command Prompt (Admin)
- In the Command prompt type: regsvr32 c:\windows\system32\mscomct2.ocx
- For Windows 7 and above type: regsvr32 c:\windows\syswow64\mscomct2.ocx
Scroll down the list until you see Microsoft MonthView Control 6.0 (SP4) and check the box beside it. Then click OK
and you should now have a new Monthview control in your Toolbox
Click on the Monthview control then click on the Userform to insert one.
Now we need to add all the other controls which are:
|Staff Name||Combo Box|
|Client Name||Combo Box|
|Food & Drink||Textbox|
|Receipts Supplied||Option Buttons x 2|
|Enter Expenses||Command Button|
We also need to enter labels for all of the text boxes and combo boxes, a frame around the option buttons, and we need to give all the controls meaningful names and captions where necessary.
Now would be a great time to watch the video as it will be much clearer watching me do all of these things than trying to follow complicated written instructions. In the video I show you how to use tables to populate the combo box lists, and how to use the Format tools to align controls quickly and get your form layout looking great. It's ok, I'll grab a cup of tea and wait here while you watch it.
What you should end up with is something like this.
Writing the VBA code
We need VBA to make this userform do anything and we only need a few subs to do this. Four of these consist of only a few lines of code (including the sub declarations), who said Userforms were hard to learn?
The code we need to write will:
- Initialize the form
- Open the form
- Close the form
- Clear the form
- Enter data into our sheet
Let's start with opening the form, we'll look at initializing it later.
VBA to open the form
We'll be calling this code from the worksheet so we will put it into a code module. The rest of the code is part of the form so will be in the Userform module that we have already created.
To insert a code module, right click the project (your workbook) in the VBA editor and click Insert -> Module
The new module should be selected by default, double click it if you aren't sure, and the right hand side (the Code window) will be empty.
This is the code to display the form:
Sub ShowExpensesForm() ExpensesForm.Show End Sub
Copy and paste this VBA into the Code window for Module1 like so:
All we need to do is use the .Show method to display the form, with the form being named ExpensesForm.
To test this, go back to Excel (ALT+F11 or just switch to Excel). Insert a shape on the sheet, add some useful text like Enter Expenses and change the color/format to your liking. Mine looks like this:
Right click the button and choose Assign macro
Our macro is called ShowExpensesForm so select that and click OK.
Click on the sheet to deselect the button, the click the button again and your macro should now run and open the form.
VBA to close the form
This is pretty simple too. This code goes in the Userform module so let's go back to the VBA editor (ALT+F11) and double click the Userform module, which in my case is called ExpensesForm.
This displays the form. To add code to any control, double click on the control. So, double click the Close button and you should see this
The VBA editor has entered the declaration for the _Click() action on the CloseForm button. This code will be executed when someone clicks the Close button. We just need to add one line of code.
Private Sub CloseForm_Click() Unload ExpensesForm End Sub
Now when you open the form and click the Close button, the form disappears.
VBA to clear the form
Clearing the form requires us to reset all the input controls to either empty values for text boxes, FALSE for Option Buttons, and -1 for Combo Boxes. This applies just to our form, with other controls there will be other code required. For Combo Boxes the -1 value indicates that nothing in the list is selected.
I'm going to write a Sub to do this job for me and I will call this sub when I want to clear the form and when I want to initialize it. I actually don't need to call it when I initialize the form as there won't be anything entered/selected when the form is first opened. When we close the form we are Unloading it too, as opposed to just hiding it, so the form is re-initialized when we next open it.
But, if you wanted to initialize your form with certain default values you could do it in a similar way to this, by calling an initialization sub. So I will include the code and show how to call it here so that it may help someone understand the process.
Here's the sub to clear the form
Private Sub ResetForm() Dim Ctrl As MSForms.Control For Each Ctrl In ExpensesForm.Controls Select Case TypeName(Ctrl) Case "TextBox" Ctrl.Text = "" Case "OptionButton" Ctrl.Value = False Case "ComboBox" Ctrl.ListIndex = -1 End Select Next Ctrl ChosenDate.Value = Date End Sub
To make use of this, double click the Clear button on the form designer in the VBA editor and you will see this
Copy/paste the code above into the top of the module, and enter the name of the sub into the ClearForm_Click() module like so:
VBA to initialize the form
This code is executed anytime the form is first displayed. In this scenario, this is every time we open it as when we close the from we are Unloading it ( Unload ExpensesForm). This is distinct from simply hiding the form ( ExpensesForm.Hide). Hiding it just doesn't show it on screen, and any selections or entered data is preserved until the next time the form is displayed.
So, we actually don't need to do this here, but I include it here as an example in case you want to know how to call some code when initializing your form.
Double click one of the buttons to access the form code, or you can right click the userform module and choose View Code
and copy/paste this code
Private Sub UserForm_Initialize() ResetForm End Sub
VBA to enter data on the sheet
Finally, the important bit 🙂 . As we are entering data into a table, we can use the ListRows.Add method to add a new row to the bottom of our table and make things easy for us. Tables are great and if you don't use them you should start now.
Once we've added our new row, we just enter the values of each of our controls (the text boxes, combo boxes and option buttons) into the appropriate cell in the row.
Double click the Enter Expenses button in the form designer, and copy/paste this code into the EnterExpenses_Click() sub
Dim LastRow As Range Dim ExpenseTable As ListObject 'Add row to bottom of Expenses table ActiveSheet.ListObjects("Expenses").ListRows.Add 'Enter data from form into our new row Set ExpenseTable = ActiveSheet.ListObjects("Expenses") Set LastRow = ExpenseTable.ListRows(ExpenseTable.ListRows.Count).Range With LastRow .Cells(1, 1) = ChosenDate.Value .Cells(1, 2) = StaffName.Value .Cells(1, 3) = ClientName.Value .Cells(1, 4) = Description.Value .Cells(1, 5) = Airfare.Value .Cells(1, 6) = Accommodation.Value .Cells(1, 7) = GroundTransport.Value .Cells(1, 8) = FoodDrink.Value .Cells(1, 9) = Misc.Value If ReceiptsYes.Value = True Then .Cells(1, 10) = "Yes" Else .Cells(1, 10) = "No" End If End With
All we need to do now is test the form. We've tested each of the buttons as we've been going along but you may decide that you want to alter the form layout, colors, labels etc.
Download the workbook
A workbook containing the form and code can be downloaded as a .xlsm. Make sure your browser saves it with a .xlsm file extension.
Sharing is Caring
If you liked this or know someone who would like to learn how to create Excel forms, please click the buttons below to share it with your friends and colleagues.