Illustrated Excel 2019 | Module 9: SAM Project 1a Entrée Sales
AUTOMATE WORKBOOK DATA
· Excel Projects Help
GETTING STARTED
1. Open the fileIL_EX19_9a_FirstLastName_1.xlsm, available for download from the SAM website. Files downloaded from the SAM website 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.
2. Save the file as IL_EX19_9a_FirstLastName_2.xlsm by changing the “1” to a “2”.
a. 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.
3. With the file IL_EX19_9a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
a. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
4. To complete this project, you need to add the Developer tab. If this tab does not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the Developer check box, and click OK.
PROJECT STEPS
1. Darnell Gilliam is a sales manager at Entrée Sales, a merchandising company that provides services to retail stores. Darnell's area of concentration is grocery stores. He is using an Excel workbook to track the visits his sales representatives made and wants to eventually use the workbook to accept requests from customers. He asks for your help in automating the workbook. Go to the Week 1 worksheet, which lists the number of visits Entrée Sales representatives made to stores during the week. Add a title to the worksheet as follows:
a. Display the Developer tab on the ribbon, and then run the Title macro to insert a title for the worksheet.
b. Edit cell A1 to add 4/12/21 as the date at the end of the title.
2. Darnell wants to use a button to insert totals in the range G4:G13, and has already created a macro named Totals that performs this action. Set up a button to run the macro as follows:
a. Assign the Totals macro to the Totals button in row 2.
b. Use the Totals button to run the Totals macro.
3. Darnell likes to keep track of the top three number of visits each week and wants to use the Top 3 button to identify them. Identify the top three number of customers serviced 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 B4:F13.
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. Stop recording the macro.
4. Assign the Top macro to the Top 3 button so that Darnell can identify the top three values by clicking the button.
5. Darnell created a macro named Bottom that highlights the bottom 10 percent of values. However, Darnell wants the macro to highlight 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 bottom 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. Use the Bottom 3 button to run the Bottom macro and highlight the bottom three customer service values for the week.
6. Go to the Customer Request worksheet, which serves as a form for entering customer information. The worksheet is not finished, and Darnell asks you to complete it. In cell B25, enter a formula without using a function that displays the customer's last name from the cell named Last_Name (cell D4).
7. Entrée Sales representatives can perform one of five activities when they visit a customer's store. Add a fifth activity to the Activities section as follows:
a. In cell F9, insert an Option Button (Form Control).
b. Edit the option button text to use the Special Setup to replace the placeholder text.
c. Format the new option button to link it to cell $K$26.
d. Position the new option button so that it is aligned with the top of the End Cap option button to its left.
8. Darnell asks you to also add a check box to the Store Type section.
a. In cell C14, insert a Check Box (Form Control).
b. Edit the check box text to use Specialty to replace the placeholder text.
c. Format the new check box to link it to cell $N$25.
d. Position the Specialty check box so that its box aligns with the Gourmet box below it and with the Full-service grocery box to its left.
9. Darnell created a macro named ClearForm that clears the form for a new customer request. To make it easy to run the macro, add a button to the form as follows:
a. In cell B10, insert a Button (Form Control).
b. Edit the button to use Clear Form to replace the placeholder text.
c. Assign the ClearForm macro to the button.
d. Format the new button to change the height of the new button to 0.4" and the width to 1.3".
e. Position the Clear Form button to align with the top and bottom of the Save button on its left.
10. Before testing the new button, Darnell asks you to change the text of the Save button so that it is more specific. Use the text Save Form as the caption of the command button.
11. Now that the Customer Request worksheet is complete, Darnell asks you to test it. Test the controls on the Customer Request worksheet as follows:
a. Save the workbook as a macro-enabled workbook, and then enter the data shown in Table 1.
b. Click the Save Form button, and then unhide the Customer Information worksheet to make sure the data is recorded in the worksheet.
c. Hide the Customer Information worksheet, return to the Customer Request worksheet, and then click the Clear Form button to clear the entries.
Table 1: Data for the Customer Request Worksheet
Item to Enter | Data First Name (cell B4) | Ann Address (cell B5) | 15 Pine Drive City (cell B6) | Troy State (cell B7) | NY Postal Code (cell B8) | 12180 Last Name (cell D4) | Moore Email (cell D5) | amoore@example.net Main Phone (cell D6) | 518-555-1238 Store Name (cell D7) | Troy Market Visit Type (cell F4) | New Customer Activities (cell D9) | New Product Store Type (cell A16) | Natural foods
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 SAM website to submit your completed project.
Final Figure 1: Week 1 Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved.
Final Figure 2: Customer Request Worksheet