You can use Excel programming to automate a lot of your employees’ monthly reporting. For example, if your employees submit a monthly travel expense report, you can develop an interactive application, such as the one that follows, that does a lot of the work for them. Follow these steps:
- Open a blank workbook.
- In A1, enter Category.
- In B1, enter Expense.
- In A2, enter Meals.
- In A3, enter Airfare.
- In A4, enter Ground Transportation.
- In A5, enter Tips.
- In A6, enter Lodging.
- In A7, enter Total.
- Press [Alt][F11].
- In the Project-VBAProject pane, double-click ThisWorkbook under VBAProject.
- Go to Insert | Procedure.
- Click in the Name text box and enter SumExpenses, then click OK.
- Enter the following code at the prompt:
ActiveCell.Value = InputBox("Enter Total Meals including tips.")
ActiveCell.Value = InputBox("Enter Round-trip Airfare")
ActiveCell.Value = InputBox("Enter total for taxis, tolls, rented cars, etc")
ActiveCell.Value = InputBox("Enter Other Tips")
ActiveCell.Value = InputBox("Enter Hotel expense")
ActiveCell.Value = "=Sum(B2..B6)"
ActiveWorkbook.SaveAs Filename:="Travel Expense Report.xls"
- Press [Alt]Q.
- Press [Alt][F8].
- Click on SumExpenses in the Macro list.
- Click the Options button.
- In the Ctrl+ box, enter e, then click OK.
At the end of the month, you can send a copy of the worksheet to each employee. After opening it, they can press [Ctrl]E and then follow the prompts to fill in the report.
Miss an Excel tip?
Check out the Microsoft Excel archive, and catch up on other Excel tips.
Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.