Exp22_Excel_Ch12_HOE – Invoice 1.0

Exp22 Excel Ch12 HOE Invoice 1.0

Excel Chapter 12 Hands-On Exercise – Invoice 

Project Description:

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
Range(“E3”)=Date
 

‘Adds   bold format to cell E3
Range(“E3”).Font.Bold=True
 

‘Protects   worksheet using the password Expl0r!ng
Worksheets(“Customer   Invoice”).Protect Password:=”Expl0r!ng”
 

End   Sub
 

  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:
 

FunctionArea(Length,Width)
 

‘This   function will calculate the area of the lawn being serviced
Area   = Length * Width
 

End   Function
 

  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.