Need help with the code for an active x command button*** (all detailed instructions are below)

The following spreadsheet is used to manage the jobs associated with a local crane company. The table at the bottom ("CLIENT LIST") represents the clients of the company. The date in the Paid Thru column of the client list represents the last date at which the client paid outstanding invoices and was fully paid up. Any jobs for the company after that date are not yet paid. The table at the top represents a list of recent jobs, some of which may yet be unpaid.

There are two worksheets associated with this workbook. The second worksheet is shown in the following figure. The client name and address at the top of the invoice are automatically updated using VLOOKUP functions in Excel formulas. But the contents of the table must be populated from VB code.

The objective of this exercise is to write code for the Build Invoice button so that it searches through the Job Archive table and finds all jobs associated with the selected client that are not yet paid and compiles a list of unpaid jobs on the invoice sheet. This sheet can then be printed and sent to the client.

Click here to download a copy of the spreadsheet. Then do the following:

a. Enter a formula for the Total column (H9:H32) on the Jobs page that computes the job total as the Hours times the Rate.

b. Write the code for the Build Invoice button. The code should do the following:

(i) Clear the contents of the invoice table (A9:G34).

(ii) Copy the values in cells B4 and B5 on the Jobs sheet into two variables: myclient (string) and mydate (date).

(iii) Loop through all of the rows in the Job Archive table and for each row where the client in column A is equal to myclient and the date in column B is greater than mydate, copy the remaining contents of the row (columns B-H) into the next empty row in the Invoice table. To do this, you will need and IF statement inside your loop.

(iv) After the table is complete, activate the Invoice sheet (i.e., bring it to the front).

Hints

1) For your loop you will want to have two variables that work as row counters. One variable (jobrow for example) will be your main looping variable and will be used to keep track of what row you are looking at in the job list. You will initialize this to the first row in the table and increment it at the end of the loop and keep looping until you encounter an empty row (based on the contents of column 1). You will have another variable (invoicerow for example) that will keep track of the next available row in the invoice table. Initialize it to 9 and then increment it only after you copy a job to the invoice table. That way it will be pointing to the next available row for the next time you find a job to copy over.

2) When you are copying jobs over to the invoice table, you don't need to use copy-paste; you can just write the contents of the cells one at a time using assignment statements. For example:

Sheets("Invoice").Cells(invoicerow, 1) = Sheets("Jobs").Cells(jobrow, 2) Sheets("Invoice").Cells(invoicerow, 2) = Sheets("Jobs").Cells(jobrow, 3) etc.

Note that you need to explicitly reference the sheet that each cell is associated with since you are working with two separate sheets.

Build invoice Selected Client Paid thru Franklin & Associates 9/10/2007 00W 5 6 Total Hours 5.6 83 2.2 3.5 8.3 12.0 245 8 Client 9 ACME Construction 10 Franklin & Associates 11 Morrison Buildin Supply 12 Johnson Construction 13 Franklin & Associates 14 Franklin & Associates 15 ACME Construction 16 Franklin & Associates 17 Johnson Construction 18 Mapleton City 19 Johnson Construction 20 ACME Construction 21 Johnson Construction 22 Jones Concrete 23 Morrison Building Supply 24 Mapleton City 25 ACME Construction 26 Franklin & Associates 27 Franklin & Associates 28. Morrison Building Supply 29 Mapleton City 30 Franklin & Associates 31 Morrison Building Supply 32 Johnson Construction 33 34 35 36 Client 37 ACME Construction 38 Mapleton City 39 Franklin & Associates 40 Morrison Building Supply 41 Johnson Construction 42 Jones Concrete 43 44 Date 8/23/2007 9/23/2007 10/1/2007 10/3/2007 11/2/2007 10/30/2007 9/12/2007 9/25/2007 9/29/2007 10/23/2007 10/16/2007 10/25/2007 9/8/2007 9/26/2007 8/23/2007 7/23/2007 8/23/2007 8/12/2007 9/22/2007 8/30/2007 8/13/2007 8/17/2007 9/27/2007 10/22/2007 JOB ARCHIVE Address Crane 200 E 388 N. Prova 40-2 128 E Elm St, Lindon 40-3 124 E 53 S, PG 40-4 320 N Circle CL, Orem 50-1 983 Walker Way Alpine 50-3 282 L 32835 Murray 50-4 200 E 388 N Provo 60-2 128 E Elm St, Lindon 50-6 124 E 53 5, PG 50-7 320 N Circle Ct, Orem 60-2 983 Walker Way Alpine 65-1 282 E 3288 S, Murray 75-1 200 E 388 N Provo 75-2 128 E Elm St, Lindon 75-3 124 E 55 SPG 70-1 320 N Circle Ct, Orem 70-2 983 Walker Way Alpine 70-4 282 E 328.85. Murray 210-1 200 E 388 N. Provo 120-1 128 E Elm St. Lindon 130-1 124 E53 S.PG 210.1 320 N Circle Ct, Orem 240-1 983 Walker Way Alpine 240-1 282 E 3288 5. Murray 70-2 Operator Lowe, Chad Shaffer Rory Schwanawelat Mike Africa. Ockert Christensen, Evan Grant, Tim Vanburen, Ockert Holland, Jenny Jones, Glen Jones, Aaron Liddel, Scott Christensen, Evan Wagstaff Ronnie Hallmark, Brandon Warstaff, Talmare Wagstaff, Jim Hale Grer Shaffer Ron Schvaneveldt. Tyson Perry, Lincoln Pechal Paul Macintoch, will Wright. Patrick Olsen Jeff 38.0 290 128 84 37.3 12.5 $ $ $ $ 5 $ 5 S $ $ $ s $ $ $ $ $ $ $ $ s $ $ 5 Rate 175.00 175.00 175.00 235.00 265.00 265.00 90.00 110.00 11000 110.00 11000 110.00 140.00 175.00 175.00 175.00 235.00 265.00 265.00 40000 400.00 11000 110 00 110 00 1.2 80.0 28.2 8.0 7.4 100 12.0 20 35.2 110 CLIENT LIST State Utah Utah Address 248 N. Wilcot 277 N. 200 E 13782 5. State 838 S3832 W 8278 N. 2892 E 421 5. 100 W city Sandy Mapleton Midvale Lehi West Valley City Santaquin Utah Zip 83799 86376 83798 82728 88278 84655 Phone Fas 801-389-8982 801-837-2828 801-223-8388 801-238-7368 809-997-3736 801-762-9876 801-282-8278 801-282-8737 802-278-8827 801-882-9478 801-754-8282 801-754-0892 Paid Thru 8/24/2007 9/1/2007 9/10/2007 6/4/2007 7/31/2007 8/8/2007 Utah Utah Utah G B D m Crane Operator Hrs Rate Subtotal 1 Invoice 2 3 Franklin & Associates 4 3782 S. State 5 Midvale, Utah 83738 6 801-997-3736 7 8 Date Address 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Total: $ Build invoice Selected Client Paid thru Franklin & Associates 9/10/2007 00W 5 6 Total Hours 5.6 83 2.2 3.5 8.3 12.0 245 8 Client 9 ACME Construction 10 Franklin & Associates 11 Morrison Buildin Supply 12 Johnson Construction 13 Franklin & Associates 14 Franklin & Associates 15 ACME Construction 16 Franklin & Associates 17 Johnson Construction 18 Mapleton City 19 Johnson Construction 20 ACME Construction 21 Johnson Construction 22 Jones Concrete 23 Morrison Building Supply 24 Mapleton City 25 ACME Construction 26 Franklin & Associates 27 Franklin & Associates 28. Morrison Building Supply 29 Mapleton City 30 Franklin & Associates 31 Morrison Building Supply 32 Johnson Construction 33 34 35 36 Client 37 ACME Construction 38 Mapleton City 39 Franklin & Associates 40 Morrison Building Supply 41 Johnson Construction 42 Jones Concrete 43 44 Date 8/23/2007 9/23/2007 10/1/2007 10/3/2007 11/2/2007 10/30/2007 9/12/2007 9/25/2007 9/29/2007 10/23/2007 10/16/2007 10/25/2007 9/8/2007 9/26/2007 8/23/2007 7/23/2007 8/23/2007 8/12/2007 9/22/2007 8/30/2007 8/13/2007 8/17/2007 9/27/2007 10/22/2007 JOB ARCHIVE Address Crane 200 E 388 N. Prova 40-2 128 E Elm St, Lindon 40-3 124 E 53 S, PG 40-4 320 N Circle CL, Orem 50-1 983 Walker Way Alpine 50-3 282 L 32835 Murray 50-4 200 E 388 N Provo 60-2 128 E Elm St, Lindon 50-6 124 E 53 5, PG 50-7 320 N Circle Ct, Orem 60-2 983 Walker Way Alpine 65-1 282 E 3288 S, Murray 75-1 200 E 388 N Provo 75-2 128 E Elm St, Lindon 75-3 124 E 55 SPG 70-1 320 N Circle Ct, Orem 70-2 983 Walker Way Alpine 70-4 282 E 328.85. Murray 210-1 200 E 388 N. Provo 120-1 128 E Elm St. Lindon 130-1 124 E53 S.PG 210.1 320 N Circle Ct, Orem 240-1 983 Walker Way Alpine 240-1 282 E 3288 5. Murray 70-2 Operator Lowe, Chad Shaffer Rory Schwanawelat Mike Africa. Ockert Christensen, Evan Grant, Tim Vanburen, Ockert Holland, Jenny Jones, Glen Jones, Aaron Liddel, Scott Christensen, Evan Wagstaff Ronnie Hallmark, Brandon Warstaff, Talmare Wagstaff, Jim Hale Grer Shaffer Ron Schvaneveldt. Tyson Perry, Lincoln Pechal Paul Macintoch, will Wright. Patrick Olsen Jeff 38.0 290 128 84 37.3 12.5 $ $ $ $ 5 $ 5 S $ $ $ s $ $ $ $ $ $ $ $ s $ $ 5 Rate 175.00 175.00 175.00 235.00 265.00 265.00 90.00 110.00 11000 110.00 11000 110.00 140.00 175.00 175.00 175.00 235.00 265.00 265.00 40000 400.00 11000 110 00 110 00 1.2 80.0 28.2 8.0 7.4 100 12.0 20 35.2 110 CLIENT LIST State Utah Utah Address 248 N. Wilcot 277 N. 200 E 13782 5. State 838 S3832 W 8278 N. 2892 E 421 5. 100 W city Sandy Mapleton Midvale Lehi West Valley City Santaquin Utah Zip 83799 86376 83798 82728 88278 84655 Phone Fas 801-389-8982 801-837-2828 801-223-8388 801-238-7368 809-997-3736 801-762-9876 801-282-8278 801-282-8737 802-278-8827 801-882-9478 801-754-8282 801-754-0892 Paid Thru 8/24/2007 9/1/2007 9/10/2007 6/4/2007 7/31/2007 8/8/2007 Utah Utah Utah G B D m Crane Operator Hrs Rate Subtotal 1 Invoice 2 3 Franklin & Associates 4 3782 S. State 5 Midvale, Utah 83738 6 801-997-3736 7 8 Date Address 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Total: $