Exp22_Excel_Ch12_HOE – Invoice 1.0
Exp22 Excel Ch12 HOE Invoice 1.0
Excel Chapter 12 Hands-On Exercise – Invoice
Recently, you took a position as the senior account manager at Wilhelmina’s Total Lawn Care (WTLC), a lawn care company located in Leland, Michigan. WTLC specializes in full service lawn care ranging from landscaping to weekly maintenance. The previous manager used a paper-based system to prepare expense reports, invoices, and payroll statements. However, this was a time-consuming process and required manual recalculation when any values changed. You want to start automating tasks using your extensive experience with Excel. You decide to start with the client invoice report form. Because each of the company’s billing representatives utilizes the same procedures, you will adapt an Excel template to use as a model for creating invoices. The template needs to be generic enough to accommodate a range of options, but it also needs to maintain a standard design to facilitate easy data entry. You will download an existing Excel invoice template, customize the template for your business needs, and inspect the worksheet for accessibility. Finally, you will create macros to perform a series of tasks, such as clearing the values to reset the form and finalizing the invoice for management approval.
Start Excel. Download and open the file named Exp22_Excel_Ch12_HOE_Invoice.xlsx. Grader has automatically added your last name to the beginning of the filename.
The Invoice template provides the basic design you need, but you want to add your company’s contact information. You also want to delete the sample data and remove the developer’s data validation comments.
Make the following edits to the template:
Type Wilhelmina’s Total Lawn Care in cell B2
Type Every Lawn Needs TLC in cell B3
Type Lot Area in cell C5
Type 1234 Caesar Parkway, Leland, MI 49654 in cell B27
Apply Italics format to cell B3
Delete the values in the range D6:D10
Continue editing the template by making the following structural changes.
Insert a row before row 1
Insert a column before column A
Set the column width of column A to 2
Add an outside border to the range B2:F29
Clear all data validation that was included in the original template
Rename the worksheet Customer Invoice
Prior to distributing your template, you want to remove all personal information. You will use the Document Inspector to complete the task.
Use the Document Inspector to remove all Document Properties and Personal Information.
Mac users, on the Excel menu, click Preferences and then click the Security icon. In the Security dialog box, click Remove personal information from this file on save.
The invoice will be sent electronically, and you want to make sure the Invoice template does not contain content that might cause difficulties for users. To ensure this document is compliant with accessibility standards, you will check accessibility.
Use the Accessibility Checker to check the template for issues. Once completed, make the following changes to improve the template’s accessibility.
Unmerge the following ranges C27:D27, C28:D28, C4:C5, C2:F2, D12:E12, D13:E13, D6:E6.
Set the text in cell C2 to left alignment.
Type Due on: in cell D13.
You know that some of remote staff members are using Excel 2010. You want to make sure the workbook does not have critical features that might not display when opened with the older version of the software.
Use the Compatibility Checker to check the template for compatibility with Excel 2010-2016 versions.
Mac users, on the Tools menu (or the Review tab), click Check Accessibility.
You want to add notes to the template to provide instruction to your staff. To do this you will annotate the worksheet using comments.
Clear the contents of cell C13. Then add the following comments to the template.
Cell C13 – Enter Business or Residential
Cell E3 – Insert Date
Cell E6 – Use the custom area function to calculate lot area
Cell E13 – Business jobs are due in 30 days, all others due upon receipt
After inserting comments, you decide you will automate the insertion of the date in a later step. Therefore, you will remove the comment from cell E3. You also want to edit the comment in cell E13 to make a small grammatical change.
Delete the comment in cell E3.
Edit the comment in cell E13 to reflect the following changes Business jobs are due in 30 days. All others due upon receipt
For your last step, you want to hide the comments to reduce screen clutter. Once the comments are hidden, the comment indicators will still be visible.
Hide all comments in the template.
You do not want to assume the level of Excel expertise throughout your company; therefore, you want to craft a macro that will automate as much as possible. The macro you will create automatically clears existing values in the workbook. Although the template is empty to start, users might open the template, save a workbook, and then want to use that workbook to prepare more invoices. Therefore, you want the macro to clear cells even though the original template has no values. Once completed, save the workbook as a macro enabled template.
Record a macro named ClearInvoice. Add the description: This macro clears existing values in the current invoice. when creating the macro. When run, the macro should do the following:
Delete the values in the cells E6, C13, E13, and the ranges C7:C11 and C15:E25. After deleting the values, the macro should enter the following default values.
C7 = Name
C8 = Company Name
C9 = Street Address
C10 = City, ST Zip Code
C11 = Phone
You want to make sure the ClearInvoice macro does what you want it to do. You will add some sample data and run the macro.
Enter the following sample values, then run the ClearInvoice macro.
C8 = John Doe Inc.
C9 = 123 Sample Street
C10 = Leland, MI 49654
C11 = (231) 555-5555
Your sales reps may not be Excel experts and not know how to run a macro. To make it easier to clear values from the form, you want to assign the ClearInvoice macro to a button. The users can click the button to clear the form to use it for another customer. You will also add a button to be utilized later in the project.
Insert a Button (Form Control) in the range H2:J2. Edit the button label to display the text Clear Form and assign the ClearInvoice macro. Next insert a Button (Form Control) spanning the range H4:J5. Edit the button text to display the text Insert Date.
Type Customer Name in cell C7 and then click the Clear Form button to run the macro.
In order to use VBA to automatically insert and format the date, you will first create a new module. You will then use the range object to insert the current date and adjust the font property to bold the current date.
Create a new module in the VBA Editor named DateStamp. Enter the following code to create the desired sub procedure.
Sub DateStamp ()
‘Unprotect worksheet for editing
Worksheets(“Customer Invoice”).Unprotect Password:=”Expl0r!ng”
‘Insert current date in cell E3
‘Adds bold format to cell E3
‘Protects worksheet using the password Expl0r!ng
Worksheets(“Customer Invoice”).Protect Password:=”Expl0r!ng”
After entering the code exit the VBA Editor and run the macro to test your work.
After running the sub procedure, you decide that the inserted date should be italic instead of bold. You will make this change in the VBA Editor by changing the Italic property. You will also add a statement to the ClearInvoice Macro to unprotect the worksheet when clearing its data.
Open the DateStamp module in the VBA Editor. Locate the code Range(“E3”).Font.Bold=True and replace the word Bold with italic.
Next open Module 1. Press enter twice at the end of the first line (Ln1, Col9) and type the following code.
‘Unprotect worksheet for editing.
Worksheets(“Customer Invoice”).Unprotect password:=”Expl0r!ng”
After entering the code save and exit the VBA Editor. Next assign the DateStamp macro to form control button 2.
You decide to create a custom function to enable users to manually calculate the area of a yard being serviced.
Open the VBA Editor and create a new module named CustomFunction then enter the following code:
‘This function will calculate the area of the lawn being serviced
Area = Length * Width
Save and exit the VBA Editor. Type =area(20,45) in cell E6 to test your work.
Insert a new worksheet named Code.
Open the VBA Editor, open the ClearInvoice module, and copy the code. Paste the code in the Code worksheet starting in cell A1 then remove any rows containing blank cells. When complete, the code block should appear continuous with no blank cells between lines.
Open the DateStamp module, and copy the code. Paste the code in the Code worksheet starting in cell A31 then remove any rows containing blank cells. When complete, the code block should appear continuous with no blank cells between lines.
Open the Function module, and copy the code. Paste the code in the Code worksheet starting in cell A44 then remove any rows containing blank cells. When complete, the code block should appear continuous with no blank cells between lines.
Close EXP22_Excel_Ch12_HOE_Invoice.xlsx. Be sure to save the copy as a .xlsx file, not a macro-enabled workbook. Exit Excel. Submit the .xlsx file as directed.