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.
Update
Since this article was written a new, better way to implement a calendar/date picker in user forms has been created.
I recommend that you use this new Multi-language Calendar for User Forms.
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.
Searching for Data With a User Form. Create a userform to search for data on the worksheet.
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.
Update
As mentioned above, this old form control is now outdated.
You should use this new Multi-language Calendar for User Forms.
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.
If you can't find the MonthView control in your Additional Controls you will need to download it. 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 a 64-bit version of Windows, 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, then right click it and choose 'Run as Administrator'
- Windows 8: Windows Key + X, then click/touch Command Prompt (Admin)
- Windows 10: click on the Windows button (bottom left of the screen), type cmd, wait for the cmd.exe file to be found, then right click it and choose 'Run as Administrator'
- In the Command prompt type: regsvr32 c:\windows\system32\mscomct2.ocx
- For 64-bit Windows 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
Enter your email address below to download an Excel workbook containing the form and related code.
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.
Hello!
It’s been a few years since you’ve posted this, but I’m trying to work this form to help streamline my company’s spreadsheets. I was wondering how you would serialize each entry on the worksheet onto the excel table. For example Build#: 1,2,3,etc. Would it be a loop function that checks if there is already a number? A function that grabs the maximum for a column and adds +1 to the value?
Basically, I want the build number to be automated sequentially rather than have the user look it up for the last entry and manually enter it, since the way I’ve set up the data from would autopopulate about 8 entries at a time.
Thank you for the resources, they’ve really helped
Hi,
Easiest way in VBA is to use the excel sheet MAX function.
NextID=Application.Worksheetfunction.Max(Table1.ListColumns(“ID”).Range)+1
Hello Phil,
This was a fantastic tutorial on how to build a Form within Excel. I was able to follow the tutorial exactly – however, my application differs. I have a table of static data that I need to reference in the Form and fill out some additional detail within that form to be reflected in the row of data. I am not sure how I would bring in the static data to my form and update the row data as needed. Is this possible? If so, would it be more helpful if I supplied files or additional details?
Thanks Greg, glad you found it useful. Yes that all sounds possible.
If you start a topic on our forum you can attach the files there and I’ll see what I can do to help.
https://www.myonlinetraininghub.com/excel-forum
Regards
Phil
Hi
Madam Philip Treacy
kindly send me literature of building of form (excel) with codes in pdf
Hi,
Sorry we don’t have PDF’s for this, you can find all the blog posts on forms here
https://www.myonlinetraininghub.com/tag/userforms
Regards
Phil
Hi
Treasy
kindly send me method of excel form, from beginning
Hi Salim,
Here it is, in this page you will find everything from our site related to userforms.
Hi Phil,
as I’ve said elsewhere, this is a most impressive piece of work which I’ve shamelessly adapted for our own use (ok, if anyone asked, I freely admitted that’s it’s not all my own work and MOTH is credited in the modules)
it was demonstrated, tested and really liked; until it was used for real:
you can’t copy text from another spreadsheet to paste in whilst the form is open (emails or other docs, no problem)
which was kinda one of the points of it in the first place (providing a standard, compact way of feeding data from a variety of different sources into a very wide table)
looks like we’ll have to type entries into the form manually or paste spreadsheet items into rows of the table directly
so close
jim
.E.:
we kinda got round this by previewing the spreadsheet in an email rather than opening it directly
Hi Jim,
In visual basic editor, form properties, the ShowModal property is set by default to true. Change it to False and you will be able to do what you need.
Brilliant!
this just gets magicker and magicker
Mulțumesc Catalin!
You’re welcome Jim 🙂
Really great tutorial.
Can you help please, I wish to use this user from where the employee takes out a pre-numbered document that is already on the spreadsheet so when they use your user form they enter the number in your user form and it takes them / finds that number on the spreadsheet and the data they enter is placed beside that number.
Thanks Philip.
We’d really need to see your workbook and data in order to be able to come up with a proper answer for you.
Can you please open a topic on the forum and attach the workbook.
Regards
Phil
This is a great tutorial; clear and detailed.
Adapting this for my own needs would fulfil my goal perfectly. My only problem is the Date Picker. I guess using a 64 bit OS and Excel 2016 64 bit is my current nightmare.
Anyway, I will see if anybody can help me out in forum.
I am new to this forum, but I think I will become an avid viewer…
I’s not an error in Date Picker, it’s how you use the objects. A Frame has a .Caption property, it does not have a .Value property.
Anyway, using Frame1.caption will not return a date from the calendar.
Use instead:
With lastrow
.Cells(1, 1) = Calendar1.Value
.Cells(1, 2) = BulkCode.Value
End With
Hi,
Love this tutorial. It’s exactly what I’m looking for, but unfortunately because I require the spreadsheet to be shared I’m unable to apply all Sub coding (where referencing adding data to tables).
Is there a way to adapt this to reference sending the form data to cell ranges not set to tables?
Zoe
Hi Zoe,
No idea why you’re saying that the code referring to tables cannot be used in a file that will be shared. The code will work exactly the same, no matter if you refer to a table cell or to a worksheet cell.
Here is what you can do:
Set NextRow = ActiveSheet.Cells.Find("*",ActiveSheet.cells(1),,,xlByRows, xlPrevious).Row+1
'add data to next row
ActiveSheet.Cells(NextRow, 1) = ChosenDate.Value
ActiveSheet.Cells(NextRow, 2) = StaffName.Value
ActiveSheet.Cells(NextRow, 3) = ClientName.Value
ActiveSheet.Cells(NextRow, 4) = Description.Value
ActiveSheet.Cells(NextRow, 5) = Airfare.Value
ActiveSheet.Cells(NextRow, 6) = Accommodation.Value
ActiveSheet.Cells(NextRow, 7) = GroundTransport.Value
ActiveSheet.Cells(NextRow, 8) = FoodDrink.Value
ActiveSheet.Cells(NextRow, 9) = Misc.Value
If ReceiptsYes.Value = True Then
ActiveSheet.Cells(NextRow, 10) = "Yes"
Else
ActiveSheet.Cells(NextRow, 10) = "No"
End If
Hi. I have been searching for this answer for a long time. It looks great, however, I get an error message that “Could not load an object b/c it is not available on this machine.”
How do I determine what object is missing?
Hi,
Press Alt+F11 to open VB Editor. In Editor menu, go to Tools-References, check if one of them has MISSING prefix.
You should also try to open the form in safe mode, without any add-in activated (keep Ctrl key pressed when opening the file, make sure there is no open excel file before opening in safe mode.)
If it works, one of the add-ins is causing the problem.
Hello! I am trying to create a form to capture my cattle farm data and produce reports, emulating the travel expense report, buh, so so hard.. Can I send the table for you look at it and help please!
Thank you.
Francis
Hi Francis,
Sure. Please post your question on our Excel forum where you can upload an Excel file with your sample data and desired result. We can then help you with the solution.
Mynda
Thank You, Thank You, Thank You.
I finally made it. I spend two days trying to figure it out, is because I’m new in excel. Coding was the difficult part, changing names and make it work… I almost died of a heart attack LOL. Important, It WORKS. Thank you again. Did I thank you enough.
Hi Jannette,
We’re happy to hear you managed to make it work, good job!
Cheers,
Catalin
It is a great lesson. Thanks.
One question : when there is hundreds of rows are entered into the form, if we wanna double check any mistyping from back up paper into form, how should we do?
maybe a scan search based on date or client name etc.
Hi An,
There is no double check based on paper, other than manual evaluation. There can be valid client names that have only a single character different than the other, like Maria and Marian for example. A fuzzy lookup needs a database of valid entries, but the result is unreliable, because will return partial matches.
Please add the “search button ” also in the user form. and give the process to create interactive user form in excel with soft copy example.
Hi Sunil,
Where should the “Search Button” search? Not sure what you mean, you have to provide more details. This article does describe the process of creating interactive user forms, there is also a downloadable file at the end of the article.
Cheers,
Catalin
Thank You
Hi, Is there any way for the data to fill out into a separate sheet?
Thanks!
Hi Kara,
Of course it’s possible, here is the place where you can change the destination:
‘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”)
You can change it to:
‘Enter data from form into our new row
Set ExpenseTable = ThisWorkbook.Worksheets(“Sheet1”).ListObjects(“Expenses”)
‘Add row to bottom of Expenses table
ExpenseTable.ListRows.Add
You should create the table in the sheet you want before running the code, it is expecting to find a table there, it will not create it from code.
Hi Catalin,
The code is working fine if the button to run the form is placed within the active sheet. However I want to place the button in Sheet1 and the form to add data into Sheet3. I tried the modification suggested by you here but it’s giving me “Compile Error: Variable not defined” and the word “Expenses” is highlighted in this line ThisWorkbook.Worksheets(“Sheet3”).ListObjects(“Expenses”). I guess the table named Expenses is not getting recognized when placed in a separate sheet. Can you please advice? Thanks.
Hi Anushree,
It’s really difficult to debug code without having the code in front of you.
Please start a topic on the forum and attach your workbook so we can run through your code ourselves.
Regards
Phil
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.
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
I am getting an error while running the macro
Subscript outof range
ActiveSheet.ListObjects(“Expenses”).ListRows.Add
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
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
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
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
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
Nice Phil,I want to save workbook before close.Could you help design a vba code with some explanation?
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
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
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 🙂
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?
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
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.
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
I am so excited. This is what I have been looking for since I was told I can’t use Access anymore.
🙂 Great, hope you can do what you need with this.
Phil
It would be perfect if you could include “update” and “delete” functions to manipulate the data entries furthermore. I do expect for it. Thanks.
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
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
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
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
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
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
Thanks Aaron, good work.
I wrote some more code too which allows the form to insert, update and delete records in the table.
Phil
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.
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
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
Thanks Vered.
If you open a Helpdesk ticket and send me your workbook I’ll take a look.
Regards
Phil
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?
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
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?
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
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.
Thanks Wayne 🙂
I’m really pleased it was useful for you.
Phil
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!
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
https://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
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!
Thanks Paul.
Ah the joys of debugging and erroneous info from the debugger 🙁
Good work on figuring it out.
Phil
This was great!!! I never knew this was something you could do with Excel. The video made the process seem so easy. Thanks.
You’re welcome Ira. Great to know we’ve opened up a new area for you to explore in Excel.
Phil