Description
Start Excel. Download and open the file named EXP22_Excel_Ch12_Cumulative_Budget.xltx. Grader has automatically added your last name to the beginning of the filename. | 0 | |
2 | Delete the sample values in the range B6:E15 and cell D2. | 0 |
3 | Clear all existing data validation in the range A1:K15. | 0 |
4 | Record a macro named Sort, be sure to use relative references. Ensure the macro sorts the data in descending order based on Amount (column F). Stop the Macro Recorder and Save the workbook as a Macro-Enabled Template. | 0 |
5 | Create a form control button that spans the cell J4:L5. Assign the Sort macro and edit the button text to Sort. | 7 |
6 | Use the VBA Editor to create a new module. Type the following VBA code to create a custom Mileage function then save and exit the VBA Editor (be sure to leave a blank line between each line and before End Function): Function Mileage (Miles, Rate) 'This function will calculate mileage reimbursement Mileage = Miles * Rate | 0 |
7 | Type Mileage Calculator in cell F4. | 5 |
8 | AutoFit the width of column F. | 0 |
9 | Click or select cell G4 and test the newly created mileage function by calculating the reimbursement amount for 45 miles traveled at a reimbursement rate of .75 per mile. Delete the value in cell G4 after testing the function. | 0 |
10 | Use the VBA Editor to create a new module named ProtectWorkbook. Type the following VBA statements to create the sub procedure (leave appropriate line spacing). Sub ProtectWorkbook() 'Protect workbook using the password Expl0r!ng Worksheets("Business Trip Budget").Protect Password:="Expl0r!ng" | 0 |
11 | Insert a new module named UnprotectWorkbook. Type the following VBA statements to create the sub procedure and then save and exit the VBA Editor (leave appropriate line spacing). Sub UnprotectWorkbook() 'Unprotect workbook using the password Expl0r!ng Worksheets("Business Trip Budget").Unprotect Password:="Expl0r!ng" | 0 |
12 | Insert a Form Control Button spanning cells J7:L8 named Unprotect. Assign the UnprotectWorkbook macro to the newly created Control Button. | 7 |
13 | Insert a Form Control Button spanning the range J10:L11 named Protect. Assign the macro ProtectWorkbook. | 7 |
14 | Insert the comment Enter target trip budget in cell D2, insert the comment Please contact your supervisor if over budget in cell D4, and Insert the comment Please use the custom function =MILEAGE(MILES,RATE) in cell G4. | 9 |
15 | Inspect the document for private information and hidden properties. Remove Document Properties and Personal Information, and Headers and Footers. Do not remove Comments, Macros, Forms, and ActiveX Controls, or Custom XML Data. Note: Mac users, from the Excel menu, open your preferences, click or select Security, and then click or select the check box to Remove personal information from this file on save. Delete any headers or footers in the workbook. | 0 |
16 | Check the document for accessibility issues. Use the Accessibility Checker pane to Unmerge the Merged Cells. | 0 |
17 | Use the Accessibility Checker pane to assign the Quick Style White Table Style Medium 1. | 12 |
18 | Insert a new worksheet named Code. | 5 |
19 | Open the VBA Editor, open Module 1, and copy the code. Paste the code in the Code worksheet starting in cell A1. | 12 |
20 | In the VBA Editor, open the CustomFunction Module, and copy the code. Paste the code in the Code worksheet starting in cell A22. | 12 |
21 | In the VBA Editor, open the ProtectWorkbook, and copy the code. Paste the code in the Code worksheet starting in cell A30. | 12 |
22 | In the VBA Editor, open the UnprotectWorkbook module, and copy the code. Paste the code in the Code worksheet starting in cell A38. | 12 |
23 | Close the VBA Editor and save the workbook as an xlsx file (not Macro-Enabled). | 0 |

Explanation & Answer

Attached.
Business Trip Budget
Target trip budget
Enter target trip budget
$0.00
Total cost of the trip
$0.00
$0.00
You're under budget by
Please contact your supervisor if
over budget
Item
Description
Cost
Qty
$0.00
Enter target trip budget
Mileage Calculator
Amount Notes
$0.00
...
