EX16_XL_CH12_GRADER_CAP_HW – Apartment Inventory
For the purpose of grading the project you are required to
perform the following tasks:
Open the file e12c1Inventory.xlsx and save it as a
Macro-Enabled Template named
Note, Mac users, disregard the slicer placeholders, as they are not
used in the project.
Apply the Droplet theme. Apply the Title cell style to the cell
B2. Apply the Yellow Orange theme colors.
Unlock the ranges B15:K17 and D4:D12.
Record a macro named Sort. Ensure the macro
sorts the Inventory list in ascending order based on the dates
listed in column F.
Create a form control button beginning in the top-left corner of
cell F4 and ending in the top-left corner of cell G7. Assign the
Sort macro. Edit the button text to display the word
Use the VBA Editor to create a new module. Type the following
VBA code to create a custom depreciation function (be sure to
include the blank lines after the first line and before the last
line). Save and exit the VBA Editor.
Function Depreciation (Value, Rate)
Depreciation = Value * Rate
Insert two rows above row 12. Enter the Row heading
DEPRECIATION RATE in cell B12, and
DEPRECIATION in cell B13.
Use the Depreciation function in cell D13 to calculate
depreciation based on the Total Estimated Value on All Items (cell
D14) and the Depreciation Rate (D12).
Clear the contents in the range B17:K19.
Create a new worksheet named Code to the right
of the HOME CONTENTS INVENTORY LIST.
Open the VBA Editor and display the module containing the sort
code. Copy the code beginning with the text Sub Sort ()
and ending with the first instance of the text End Sub.
Paste the contents in cell B2 of the Code worksheet. Do not close
the VBA Editor.
Open the VBA Editor and display the module containing the
DEPRECIATION function. Copy the code beginning with the text
Function Depreciation(Value, Rate) and ending with the
first instance of the text End Function. Paste the
contents in cell B23 of the Code worksheet. Close the VBA
Save the file as e12c1Inventory_LastFirst in
the Excel file format. Close the file. Based on your instructorâ€™s
directions, submit e12c1Inventory_LastFirst.