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
- Testing
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:
| Data | Control Type |
| Staff Name | Combo Box |
| Client Name | Combo Box |
| Description | Textbox |
| Airfare | Textbox |
| Accommodation | Textbox |
| Ground Transport | Textbox |
| Food & Drink | Textbox |
| Miscellaneous | Textbox |
| Receipts Supplied | Option Buttons x 2 |
| Enter Expenses | Command Button |
| Clear | Command Button |
| Close | 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
NOTE: As well as shapes, you can assign macros to your QAT, the Ribbon or use a keyboard shortcut.
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
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
Testing
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.
melissa2910 says
Can someone please tell me how to write code so that in description box I can fill in the entire box (instead of it just being on one line).
Also once the sheet is entered-mine wont close-how do I get it to close after data is entered?
Thanks for your help.
Philip Treacy says
Hi Melissa,
In the form designer, click on the Description text box, and you should see the properties for this in the Properties Window. If you don’t, press F4.
Look for the MultiLine property and set that to True.
Not sure what you mean by ‘when the sheet is entered’? Do you mean once a line/record is added to the sheet, the form stays visible? If you want it to disappear after adding a record, then modify the EnterExpenses_Click sub. Add this line before the End Sub:
Unload ExpensesForm
Regards
Phil
Rakesh says
I am getting an error while running the macro
Subscript outof range
ActiveSheet.ListObjects(“Expenses”).ListRows.Add
Catalin Bombea says
It seems that you do not have a table named Expenses in the active sheet.
Use ActiveSheet.ListObjects(1).ListRows.Add, if you want to add rows to the first table from the active sheet, using a table index instead of the name.
Catalin
Rakesh says
Hi Catalin,
Thanks for your reply. I am trying the same example show above. The name of sheet is “Expensestb” also in the same sheet I have named the range as “Expenses”.which has the expenses details.
Again I am posting the full vba code as below. Is anything incorrect in this code or still the table name is missing.Please advice
Private Sub cmd_ee_Click()
Dim LastRow As Range
Dim Expensetable As ListObject
‘Add row to bottom of Expenses table
ActiveSheet.ListObjects(“Expenses”).ListRows.Add AlwaysInsert:=True
‘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) = cmb_staffname.Value
.Cells(1, 3) = cmb_clientname.Value
.Cells(1, 4) = txt_des.Value
.Cells(1, 5) = txt_airfare.Value
.Cells(1, 6) = txt_accom.Value
.Cells(1, 7) = txt_grotr.Value
.Cells(1, 8) = txt_fd.Value
.Cells(1, 9) = txt_misc.Value
If opt_y.Value = True Then
.Cells(1, 10) = “Yes”
Else
Cells(1, 10) = “No”
End If
End With
End Sub
Catalin Bombea says
A defined table is an object. Did you created that object by selecting the range, and pressing Ctrl+T (or from Insert tab, press on Table), choose the table name in the dialog box that will open.
You are saying that you named the range.
Maybe it’s better if you upload your sample file on our forum (create a new topic) so we can see what you have there.
Catalin
Rakesh says
Hi Catalin,
Thank you so much it’s working now. The error was due to table.
Please could you suggest the vba code. When the Enter Expenses button in clicked it should not allow any blank record to update from the textbox,combobox and option button.
Thanks
Rakesh
Catalin Bombea says
Hi Rakesh,
All you have to do is to wrap the code into a conditional test, checking the length of the text before writing the values, like:
If Len(Textbox1.Text)>0 Then
‘ rest of code
End If
rathanak says
Nice Phil,I want to save workbook before close.Could you help design a vba code with some explanation?
Philip Treacy says
Thanks Rathanak,
Do you want to use the form to save the workbook? or just automatically save the workbook before it is closed?
If you want to use the form then add a command button to the form and use code like this:
If you want to automatically save the workbook before it is closed the you will need to use an event saved in the ThisWorkbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Save End SubLet me know if you can’t get it to work.
Regards
Phil
Max says
Good Afternoon Phillip,
I have a share trading spreadsheet and I was looking at using a data form to insert details of my next trade to my spreadsheet. I don’t know if it’s possible to do what I want, I have spent some time looking around the web to no avail and took a short cut to ask you if it is possible.
I have about 60 columns, some don’t need filling in as they have formulas and automatically fill in when other data is put in. I would need to input data in about 30 fields, this I was hoping to do using the data form. Some of these fields are in between others that don’t need filling in.
The spreadsheet already have the formulas carried down the spreadsheet by many many rows.
Is it possible to use the data form in this way?
If so, using the data form and having it add a new row, will it add empty cells with no formulas in where there was formulas?
Hope this make sense and appreciate any help you might be able to supply.
Cheers … Max
Philip Treacy says
Hi Max,
If you can please open a Helpdesk ticket and attach your workbook, it’ll make it easier to understand what you are trying to do, and we can then give you an idea of whether it’s possible or not.
Cheers
Phil
PS No worries about the double comments 🙂
Charles Hall says
I want to add 2 more group boxes with yes no in them but I am lost on how to add the code to the existing code. can you explain or do you have another lesson that teaches you how to do this?
Philip Treacy says
Hi Charles,
Adding a couple more yes/no buttons wouldn’t be too hard, but what exactly do you want them to do? Do you want to check if they have been selected? Can the form be submitted if one of them isn’t selected?
If you raise a Helpdesk ticket and I’ll see what I can do to help.
Cheers
Phil
Charles Hall says
I want to check if selected. In you course you have one group box with yes/no, I want to add 2 more group boxes with Yes/No, tried to add to your existing code but cannot figure out how to make it work.
Philip Treacy says
Hi Charles,
Please raise a Helpdesk ticket and send me what you have done in your workbook and I’ll see what i can do to help.
Regards
Phil
Dean says
I am so excited. This is what I have been looking for since I was told I can’t use Access anymore.
Philip Treacy says
🙂 Great, hope you can do what you need with this.
Phil
Julian says
It would be perfect if you could include “update” and “delete” functions to manipulate the data entries furthermore. I do expect for it. Thanks.
Philip Treacy says
Hi Julian,
We could make modifications for you but we would need to understand exactly how you want these carried out. If you want to raise a Helpdesk ticket we can discuss it further.
regards
Phil
Julian says
Hi Philip,
I Just used your “template” to keep a list of my collected CDs privately. Therefore, I’ve changed each control names and caption as well as replaced a calendar control compatible to Excel 97. I have been learning something about Excel by my own partly from on-line training, like yours, for many years. Now that you suggest me to raise a Helpdesk ticket for in-depth learning, could you please explain more details about it. I think as long as I’m afford to buy it, even I’ve had retired for more than 6 years without income, I still like to keep learning what I’m interesting in.
Regards,
Julian
Philip Treacy says
Hi Julian,
It’s impossible to say what work is involved to add UPDATE and DELETE functionality to your code. there are several reasons for this. Because it is code you have written/modified we first of all need to familiarise ourselves with it and understand how to incorporate what you want into the existing code without breaking it. Or we may need to alter existing code to add the new functionality.
We’d also need a very clear explanation of how you want the DELETE and UPDATE to work – our ideas for this will probably be different to yours. For example, when we DELETE a record, are there special record identifiers that need to be updated (a unique record key)? Do we need to write a new routine to go through each record in turn asking if you want it deleted (this could take a long time) or would it be better to just delete the record from the sheet?
So all I meant was that we can do all of this for you, but we need to see your code and need clear instructions on what you want. The first step in this is to create a Helpdesk ticket and supply the workbook and instructions.
It’s great that you are keeping busy after retirement. I know that when I retire I’ll certainly want to keep busy too and coding is an excellent way to keep the brain active.
Regards
Phil
Julian says
Hi Phil,
Be honest, I don’t know how to write the code regarding those two functionalities. However, when it comes to database mangement it involves “ADD”, “CHANGE/UPDTE”, “DELETE” for records manuplation and record ID is the key to retrieve the specific record. That’s what I learned from MS Acess. That’s it. Now that I’ve learned how to ADD records via a VBA form and related controls from you. It’s a great breakthrough for me. I do appreicate it.
Regards,
Julian
Philip Treacy says
Hi Julian,
I have made a note of this in my ‘VBA Blog Topics’ list an will look at covering this in a future blog post.
Regards
Phil
Aaron S says
I had the same request like many others with the request of updating/deleting records using this form. I took what you provided us and made some changes to accomplish this. I’m sure it’s not the best way but it serves my purpose so hopefully it will help others. You can download a copy at the link below. Enjoy!
https://copy.com/itW12FEz3N3lMWtU
Philip Treacy says
Thanks Aaron, good work.
I wrote some more code too which allows the form to insert, update and delete records in the table.
Phil
Bob says
Well done Phil. And your homage to the Walking Dead wasn’t lost on viewers. 🙂
Excel forms, and the necessary VBA code, are something I’ve been interested in learning to improve my dashboards. Having a data entry form users can call up from the dashboard, rather than force users to go into the workbook to find a data entry sheet, would improve data entry integrity it seems to me.
Thanks for a very informative tutorial.
Philip Treacy says
Thanks Bob. I’ve got another post on forms coming soon. As you say they can be useful to make sure the data going into your sheets is valid.
You’re the first person to mention the WD 🙂 Can’t wait for the next season. I’m currently working my way through S5 of GoT
Cheers
Phil
Vered Samuel says
Wow – excellent tutorial!!
When I tried creating my own form, I keep getting a “Run time error 424: Object required”
with applying the macro to the “Enter Expenses” button. I seem to be missing a reference of some sort, but I’ve checked and its identical to your form. Any idea what I am missing?? Thanks in advance
Philip Treacy says
Thanks Vered.
If you open a Helpdesk ticket and send me your workbook I’ll take a look.
Regards
Phil
Stefan van Gaal says
Hi Philip,
Very clear and understandable instruction, which makes me want to give a go at userforms myself. One question though: My toolbox does not contain the Microsoft MonthView Control 6.0 (SP4). under Additional Controls. Any advice how to add this to my version of Excel?
Philip Treacy says
Thanks Stefan. If you are missing the control you can download it from Microsoft and install it. I’ve added instructions towards the top of the post. You need 32bit Excel but there is an alternative for 64 bit.
Regards
Phil
Lionel B. Dyck says
MonthView looks outstanding. If I add that to a workbook and share that workbook with others will they need to get into developers view and add it or does that only need to be done by the developer?
Philip Treacy says
Hi Lionel,
The recipient must have the MonthView control installed too. They don’t need to go into the VBA editor and do this, as long as the control (which is actually a file on the computer) is installed and registered in Windows, it should work fine.
See the section I’ve added to the top of the post regarding downloading and installing the MonthView control.
Cheers
Phil
Wayne F. Eichler says
Excellent presentation by Phil. The presentation was very clear and complete. He used a real world example in how to set everything up. It adds another tool to use and enhance your skills and add value within organizations.
Philip Treacy says
Thanks Wayne 🙂
I’m really pleased it was useful for you.
Phil
Paul S. says
Thanks, Phil. Great post! I really appreciate tips like this. It opens up a whole new world to some of us.
However, when I try your workbook, I get the message “Object library invalid or contains references to object definitions that could not be found”. When I try my own workbook, on the “ExpenseForm.Show”, it highlights the “Show” and I get the error message – “Compile error: Method or data member not found”.
I assume that in the VBA modules, under Tools, I need to add some references. Do you have any idea what references I may need to add to get this to work (or perhaps there is something else I need to do)?
Thanks!
Philip Treacy says
Thanks Paul.
Reading up on this your errors could be due to Microsoft updates applied to your computer causing conflicts, or your controls need to be re-registered.
You can try the solutions in these links to see if that fixes it for you;
https://support.microsoft.com/en-us/kb/2703186
https://social.technet.microsoft.com/Forums/en-US/473b1980-56b3-49ff-be71-3a60c0db048b/form-controls-stop-working-after-december-2014-updates?forum=excel
http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-2010-object-libraray-invalid/1fc414d6-05db-4c8a-bf26-f5a10e409dbb
Let me know if you get it resolved.
Regards
Phil
Paul S. says
Phil, Thanks for the info. Since I could not get your downloaded workbook to work for me, I assumed that it had to do with not having something selected in Tools, References in VBA. However, after further trial and research, I found out that the reason myworkbook did not work was due to not totally following through with renaming the form and other things through the rest of the VBA code. Unfortunately, it took longer to diagnose, because the debugger stopped on the VBA code of “form.Show” – which was not the issue. The real issue was in a later section of code. Thanks again for the excellent presentation!
Philip Treacy says
Thanks Paul.
Ah the joys of debugging and erroneous info from the debugger 🙁
Good work on figuring it out.
Phil
Ira says
This was great!!! I never knew this was something you could do with Excel. The video made the process seem so easy. Thanks.
Philip Treacy says
You’re welcome Ira. Great to know we’ve opened up a new area for you to explore in Excel.
Phil