Lesson 9 project

by


 PART 1

For this assignment, you will be working with macros. Although they might not seem useful at first, macros can actually save a lot of time, especially if you are repeating the same series of steps, such as formatting data, filtering and sorting information, or applying the same series of functions and operations to your sheets.

As a human resources manager at your business, you have been asked to create an address book for all current employees to use for employee-related letters. Your supervisor would like you to create an address book template that could be easily used by departments throughout the company, not just in your human resources department. You decide to create a file with an Address Book template macro that you can share with other departments.

Hands-On Project 1 Directions

  1. Open your Excel software application.
  2. Select Blank Workbook.  Save the workbook as CIS105_Lesson9Project1_MEID.xlsx. 
  3. Create a macro by completing the following steps.
    • Under the View tab, select Record Macro in the Macro group.
    • Name the macro “Addresses.”
    • Do not create a Shortcut key.
    • Select for Store Macro in: This workbook.
    • Type the following description: Use to keep track of employee addresses.
    • Click OK then complete the following steps:
      • Select Page Layout, Orientation then Landscape.
      • Select Page Layout, Margins then Narrow.
      • Change column A width to 7.00 (54 pixels).
      • Change column B width to 32.00 (229 pixels).
      • Change column C width to 42.00 (299 pixels).
      • Change column D width to 20.00 (145 pixels).
      • Change column E width to 10.00 (75 pixels).
      • Change column F width to 18.00 (131 pixels).
      • Cell range A2:A26, add numbers down from 1 to 25 (1, 2, 3, ….).
      • Cell range A2:A26, apply Arial font, size 14, Bold.
      • In cell B1, type: Name.
      • In cell C1, type: Address.
      • In cell D1, type: City.
      • In cell E1, type: State.
      • In cell F1, type: Zip Code.
      • Cell range B1:F1, apply Arial font, size 14, Bold and Center alignment.
      • Insert a new row above existing row 1.
      • Select A1 through F1, apply Merge & Center then type in: Addresses.
      • In Merge & Centered cell range A1:F1, apply Arial font, size 22, Bold.
      • Apply a dark blue fill color of your choice for cell range A1:F1.
      • Apply a light blue fill color of your choice to cell range A2:F2.
      • Select cell range A1:F27, and apply All borders.
      • When you have completed the formatting, stop recording.
  4. Rename Sheet1 tab to HR Dept.
  5. Insert a new sheet and rename it Sales Dept.
  6. Insert another new sheet and rename it Purchasing Dept.
  7. With the Sales Dept worksheet active, run your Addresses macro to make sure it functions properly.
  8. Save your workbook CIS105_Lesson9Project1_MEID.xlsm and close the file.

 PART 2

For this assignment, you will apply various security and protections to your AddressBook files you created in Project 1. Macros are great for sharing because macros can be stored in Excel spreadsheets, eliminating the need to send additional files to colleagues. You can simply write a macro, send the spreadsheet over, and let the recipient work with the file on their own. However, the spreadsheet might hold confidential information, so it is important to add layers of security and protections before sharing.

Hands-On Project 2 Directions

  1. Open your Excel software application.
  2. Open Lesson 9 Project 1 file – CIS105_Lesson9Project1_MEID.xlsm and save it as CIS105_Lesson9Project2_MEID.xlsm.
  3. In the HR Dept sheet, fill in the information for all 25 employees. The information can be real or fictitious, but you cannot repeat entries. Be sure to fill in all information.
  4. Apply center alignment to cell range B2:F27.
  5. Open the Purchasing Dept sheet and run the Addresses macro.
  6. Go into the File -> Info -> Properties ->Advanced Properties and change the following:
    • Title: Addresses
    • Subject: Employee Addresses
    • Author: Student Name NOTE: Change to your own name
    • Manager: Student Initials NOTE: Add your initials
    • Company: Rio Salado
    • Category: Tracking
    • Keywords: CIS105
    • Comments: Used to keep track of employee addresses for mailers.
  7. Click OK.
  8. Under the Review tab, add protection to the HR Dept worksheet. Select Protect Sheet.
  9. Select all checkboxes so none of the contents on the HR dept worksheet can be changed.
  10. Use the password Address. 
  11. Save the workbook as CIS105_Lesson9Project2_MEID.xlsm.