Illustrated Excel 2016 | Module 9: SAM Project 1a Five Points Media AUTOMATING WORKBOOK TASKS GETTING STARTED Open the file IL_EX16_9a_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Illustrated Excel 2016 | Module 9: SAM Project 1a

Five Points Media

AUTOMATING WORKBOOK TASKS

GETTING STARTED

Open the file IL_EX16_9a_FirstLastName_1.xlsm, available for download from the SAM website.

Save the file as IL_EX16_9a_FirstLastName_2.xlsm by changing the “1” to a “2”.

0. If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the file IL_EX16_9a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

If you see a Message Bar with a security warning at the top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file. [Mac Hint – If you see a popup with a security warning, click Enable Macros to enable the macros contained in the file.]

To complete this project you will need to display the Developer tab. To add this tab to the Excel ribbon, click the File tab to open Backstage View and then click the Options button. In the Excel Options dialog box, click the Customize Ribbon option and click the Developer check box. Click the OK button to close the Excel Options dialog box and confirm the Developer tab appears in the Excel Ribbon.

 

PROJECT STEPS

Robert Cisneros is the sales manager for Five Points Media, a company selling subscriptions to online media including movies and TV shows. He has asked you to help him automate a worksheet he will use to analyze sales data each week.

Switch to the Week 1 worksheet. Run the Title macro to insert a title for the worksheet in cell A1.

Edit cell A1 to add 1/6/2020 as the date at the end of the title.

Add totals to the worksheet as follows:

a. Assign the Totals macro to the Totals button in column H.

b. Use the Totals button to run the Totals macro.

Identify the top five sales for the week by recording a new macro as follows:

c. Enter Top as the macro name.

d. Assign the shortcut key CTRL+j to the macro. [Mac Hint – Assign the shortcut key Option+Cmd+j to the macro.]

e. Store the macro in This Workbook.

f. Enter the text Highlights the top five sales for the week as the description.

g. With the macro recording, select the range B3:F12.

h. Apply the Top 10 Items conditional formatting rule to the selected range.

i. In the Top 10 Items dialog box, use 5 as the number of top values to highlight.

j. Highlight the cells with Green Fill with Dark Green Text.

k. Stop recording the macro.

Assign the Top macro to the Top 5 button in column H so that Robert can identify the top five sales values by clicking the button.

A macro named Bottom highlights the bottom 10 percent of values, and Robert wants the macro to highlight the bottom five values. Modify the macro as follows:

l. Open the Bottom macro in the Visual Basic Editor.

m. After the .TopBottom = xlTop10Bottom line of code, change the .Rank = 10 line of code to .Rank = 5 so that the highlighting rule identifies the bottom five values.

n. Change the .Percent = True line of code to .Percent = False so that the highlighting rule selects values rather than a percentage of values.

o. Use the Bottom 5 button in column H to run the Bottom macro and highlight the bottom five sales values for the week.

Robert wants to use a button to apply data bars to selected cells and compare their values. Add a button as follows:

p. Between the Bottom 5 and Bonus buttons in column H, insert a Rounded Rectangle to match the other button shapes. (Hint: Depending on your version of Office, the name of the shape may appear as Rectangle: Rounded Corners.)

q. Set the height of the rounded rectangle to 0.3” and set the width to 1”.

r. Enter Compare as the button text.

s. Apply the Moderate Effect - Dark Green, Accent 3 shape style to the new button.

t. Assign the Compare macro to the Compare button.

The Compare macro should select the totals in the range G3:G12, but instead selects cells in the range J3:J12. Correct the macro as follows:

u. Open the Compare macro in the Visual Basic Editor.

v. Change the Range("J3:J12").Select line of code between the initial comments and the “Selection.FormatConditions.AddDatabar” lines to Range(“G3:G12”).Select so that the macro selects cells in the correct range. (Hint: There are no spaces before or after the parentheses in the code.)

w. Run the edited Compare macro.

Thursdays and Fridays are typically the busiest days of the week. For subscriptions sold on those days, salespeople receive a 10 percent bonus for sales in the top tier (indicated by a gold star) and a 5 percent bonus for sales in the middle tier (indicated by a half-gold star).

Use the Bonus button in column H to run the Bonus macro and determine which salespeople receive a bonus for their Thursday and Friday sales.

Record a new macro and assign it to a button to prepare the worksheet for printing as follows:

x. Enter Print_Setup as the macro name.

y. Store the macro in This Workbook.

z. Enter the text Sets up the worksheet for printing as the description.

aa. With the macro recording, change the orientation of the worksheet to Landscape.

ab. Set the range A1:G12 as the print range.

ac. Scale the worksheet to print on one (1) page wide and one (1) page tall.

ad. Select cell A1 to remove the selection from the range.

ae. Stop recording the macro.

af. Assign the Print_Setup macro to the Print button.

Your workbook should look like the Final Figure on the next page. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure: Week 1 Worksheet