This week, we apply the fundamentals of UserForm into a real-life project – Expense Tracker. Let’s watch this video illustrating how our Expense Tracker works:
If you are new to VBA UserForm, no worries! Check out our Beginner’s Guide: UserForm in Excel VBA where we covered the fundamentals you need to know.
Table of Contents
Existing features of Expense Tracker (inherited from last week)
- Utilize TextBox and ComboBox to guide user input
- Transfer user input to worksheet upon clicking a CommandButton and reset the UserForm
- Insert a CommandButton on worksheet to display the UserForm
New features of Expense Tracker
- Format the UserForm and Controls to optimize visual effect
- Assign initial values when the UserForm is displayed
- Dynamic ComboBox: alter items in ComboBox2 according to user’s selection in ComboBox1
- Validating user input and display error message for invalid entries
- Accommodate keyboard users by setting tab order and hot keys
Step 1. Design user interface
Before adding controls, set the Font of the UserForm so that controls added will have the same font. Name the UserForm and controls. Format controls according to the photo above.
Format the TextBox to look professional. Enable MultiLine for tb_Remarks so that a long text can be better displayed.
Step 2. Prepare the Data Worksheet
Add a table with headers. Add a CommandButton named ButtonRecordExpenses. Change its caption to “Record Expenses”. Double click the CommandButton and add “Form_ExpenseTracker.Show” to its event handler. (Refer to how to write code to display the UserForm last week)
Step 3. Assign initial values when the UserForm is loaded
Upon loading the UserForm, we want to
- Fill in tb_Date with today’s date formatted as DD/MM/YYYY, e.g. 30/01/2022
- Add two items to cb_Category: “Dining” and “Transport”.
- Display the first item of cb_Category and cb_SubCategory. By default, the first item has ListIndex of 0, the second item has ListIndex of 1, etc.
Notice that we use With-statement. The With-statement allows you to perform a series of statements on a specified object without requalifying the name of the object.
Using With-statement is superior due to
1) cleaner presentation, and
2) convenience when changing the name of the object.
The two blocks of code do the same thing, but using With-Statement is superior due to cleaner presentation and convenience
Step 4. Design a dynamic ComboBox
A dynamic ComboBox is a useful UserForm teechnique. When a different item is selected in cb_Category, it triggers its Change() event handler, and the items in cb_SubCategory changes. This is done by clearing all the items, adding the sub categories according to the category selected using If-statement, and display the first item of cb_SubCategory.
Step 5. Validate user’s entries and transfer entries to worksheet
Last week, we explored how to transfer data to worksheet and reset the UserForm for the next entry. This week, we add another feature: to validate user’s entries before transferring data to worksheet.
We utilize several If-statements to check for empty fields. A MsgBox would be prompted for empty fields. We fill in a default value and direct the user’s attention towards that field. Then, we quit the procedure using Exit Sub. Similarly, we can check if the amount is a number using IsNumeric() function.
After ensuring all entries are valid, the entries are transferred to the worksheet and the UserForm is resetted for next entry.
Step 6. Optimizing user experience of keyboard users
Some users prefer using keyboard to navigate through the UserForm by pressing Tab and Shift+Tab. There are two ways to accommodate keyboard users: tab order and accelerator keys.
Tab order
The tab order of controls determines the sequence in which the controls are activated when the user press Tab or Shift+Tab. Choose View -> Tab Order.
Use “Move Up” and “Move Down” to organize the tab order. Since we have already filled in today’s date for tb_Date, we set its tab order to the last.
Accelerator keys
You can assign a hot key to the labels next to TextBox/ComboBox by assigning accelerator key. By assigning “d” as the accelerator, the user can press Alt+d to access tb_Date, the control with a tab order behind Label1.
Summary
Your Expense Tracker is ready to be launched 🙂 In designing our real-life project – Expense tracker, we keep existing UserForm features and incorporate new features. The core design of Expense Tracker is developed. Next week, we would explore further upgrades to the Expense Tracker. Stay tuned!
Enjoy our VBA projects? Check out Template Helper, a simple yet useful VBA project illustrating VBA programming fundamentals.
Thank you for reading til the end. Please feel free to leave any comments down below 🙂 We greatly value your feedback since it is our drive to improve.
Key takeaways:
- Save the user’s time by assigning initial values to fields and optimizing tab order and hot keys for keyboard users.
- Dynamic ComboBox is useful when the items in certain ComboBox is dependent on another ComboBox.
- Validate user’s entries before transferring data to worksheet is a good practice
- Use With-statements for a cleaner code structure and developer’s convenience.