Illustrated Excel 365 | Module 9: SAM Project B Replay TV

GETTING STARTED

1. Save the file IL_EX365_9B_FirstLastName_1.xlsm as IL_EX365_9B_FirstLastName_2.xlsm

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsm file extension, do not type it. The file extension will be added for you automatically.

2. With the file IL_EX365_9B_FirstLastName_2.xlsm open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

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

3. Files downloaded from SAM are safe and do not contain viruses, but due to a recent Microsoft policy update, macros in downloaded files are disabled by default. To complete this project, you will need to enable macros in the file. To enable macros on this file:

a. For PC: Open Windows File Explorer and go to the folder where you saved the file. Right-click the file and choose Properties from the context menu. At the bottom of the General tab, select the Unblock checkbox and select Apply, and then click OK.

b. For Mac: If a dialog box about macros appears, click Enable Macros.

PROJECT STEPS

1. Jordan Mosley is a producer at Replay TV, a streaming media service that provides vintage television shows for subscribers. Jordan is using an Excel workbook to track the hours viewed for each show to determine whether to renew the show for the next season. He has created a basic worksheet for one show and asks for your help in automating the workbook so he can copy the worksheet and use it for other shows. Go to the Engagement worksheet, which lists the viewing data for one show. Display the Developer tab on the ribbon, and then run the Title macro to insert a title for the worksheet.

2. Jordan asks you to use the Top 3 button to highlight the three episodes with the most hours viewed. Identify the three episodes with the most hours viewed by recording a new macro as follows:

a. Use Top as the macro name.

b. Store the macro in This Workbook.

c. With the macro recording, select the range C7:C18.

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

e. Use 3 as the number of top values to highlight.

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

g. Click cell G1.

h. Stop recording the macro.

3. Assign and test the Top macro as follows:

a. Assign the Top macro to the Top 3 button in cell B21 so that Jordan can identify the three most-viewed episodes by clicking the button.

b. Clear the conditional formatting in the range C7:C18.

c. Click the Top 3 button to confirm it highlights the top three Hours Viewed values.

4. Jordan created a macro named Bottom that highlights the bottom 10 percent of values. However, Jordan asks you to edit the macro so it highlights the bottom three values to match the new Top macro. Modify and test the Bottom macro as follows:

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

b. After the .TopBottom = xlTop10Bottom line of code, change the .Rank = 10 line of code to .Rank = 3 so that the highlighting rule identifies the lowest three values.

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

d. Save the code and close the Visual Basic Editor.

e. Use the Bottom 3 button to run the Bottom macro and highlight the three episodes with the lowest hours viewed.

5. Replay TV has set a yearly hours viewed goal of 500,000 for each show. Jordan asks you to provide a way to display text in cell F4 indicating whether the show met this engagement goal depending on the total hours in cell C19. Create a new macro as follows:

a. Create a macro using Engagement as the macro name.

b. In the Visual Basic Editor, between the Sub Engagement() and End Sub lines, enter the code for the new macro as shown in Figure 1. Write the macro so it first refers to the value in cell C19, which displays the total hours viewed for the show. If the value is less than 500,000, the macro should display Does not meet goal in cell F4. Otherwise, the macro should display Meets goal in cell F4.

c. Save the code and close the Visual Basic Editor. [Hint: Score will be credited only after completing Step 7. ]

Figure 1: Code for the Engagement Macro

6. Create a button Jordan can click to run the Engagement macro as follows:

a. In cell F21, insert a Rectangle: Rounded Corners shape. [Mac hint: Rounded Rectangle.]

b. Apply the Colored Outline – Purple, Accent 1 shape style to the rounded rectangle.

c. Enter the text Engagement in the rounded rectangle.

d. Resize the rounded rectangle to a height of 0.35" and a width of 1.1".

e. Position the Engagement shape so its top aligns with Bottom 3 button.

7. Assign the Engagement macro to the Engagement button, and then test the button by clicking it to insert text in cell F4.

8. The check box in cell F7 corresponds to a low viewer rating of 1, 2, or 3. Clicking the 1-3 check box runs the LowRating macro, which should insert the recommendation Review status in cell F9, but is not working correctly. Modify and test the LowRating macro as follows:

a. Open the LowRating macro in the Visual Basic Editor.

b. Edit the ActiveCell.Formula statement text to display Review status instead of Blank.

c. Save the code and close the Visual Basic Editor.

d. Click the 1-3 check box to confirm it inserts the correct text in cell F9.

e. Click the 1-3 check box again to remove the checkmark. [Hint: Score will be credited only after completing Step 10.]

9. Jordan asks you to add a second check box for high customer ratings. Add the check box as follows:

a. In cell F6, insert a Check Box (Form Control).

b. Edit the check box text to use 4-5 to replace the placeholder text.

c. Assign cell $F$6 as the Cell link for the check box.

d. Position the 4-5 check box so its right edge aligns with the right edge of the 1-3 check box. [Mac hint: Update size of 4-5 check box to match with 1-3 check box.]

10. Jordan created a macro named HighRating that displays the text "Renew" if cell F4 contains the text "Meets goal" and cell F6 contains "TRUE", which it will when the 4-5 check box is checked. (Hint: The range F6:F7 is formatted to display white text so the TRUE value is not visible.) Assign and test the HighRating macro as follows:

a. Assign the HighRating macro to the 4-5 check box control.

b. Click the 4-5 check box to test the results.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Engagement Worksheet