Illustrated Excel 2019 | Module 9: SAM Project 1b Taco Gato

GETTING STARTED

1. Open the fileIL_EX19_9b_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_9b_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_9b_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. Gabriel Santos owns Taco Gato, a food truck in Fort Worth, Texas. He is using an Excel workbook to track the daily sales of menu items and accept orders for food truck catering at local events. He asks for your help in automating the workbook. Go to the Weekly Sales worksheet, which lists the food items sold from the Taco Gato truck during the previous week. Add a title to the worksheet as follows:

a. Display the Developer tab on the ribbon, and then run the Week_Title macro to insert a title for the worksheet.

b. Edit cell A1 to add 5/24/21 as the date at the end of the title.

2. Gabriel wants to use a button to insert totals in the range G4:G14, and has already created a macro named Total_Sold that performs this action. Set up a button to run the macro as follows:

a. Assign the Total_Sold macro to the Total Sold button in row 2.

b. Use the Total Sold button to run the Total_Sold macro.

3. Gabriel likes to keep track of the top four items sold each week and wants to use the Top 4 button to identify them. Identify the top four items sold 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:F14.

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

e. Use 4 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 4 button so that Gabriel can identify the top four values by clicking the button.

5. Gabriel created a macro named Bottom that highlights the bottom 5 percent of values. However, Gabriel wants the macro to highlight the bottom four 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 = 5 line of code to .Rank = 4 so that the highlighting rule identifies the bottom four 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 4 button to run the Bottom macro and highlight the bottom four items sold for the week.

6. Go to the Catering Orders worksheet, which serves as a form for entering information about catering customers and their events. The worksheet is not finished, and Gabriel asks you to complete it. In cell C25, enter a formula without using a function that displays the customer's address from the cell named Address (cell B5).

7. Customers can reserve a Taco Gato food truck for one of five durations at a catering event. Add a fifth duration to the Duration section as follows:

a. In cell F9, insert an Option Button (Form Control).

b. Edit the option button text to use the Unlimited to replace the placeholder text.

c. Format the new option button to link it to cell $K$26, if necessary.

d. Position the new option button so that it is aligned with the top of the 3 hours option button to its left.

8. Gabriel asks you to also add a check box to the Menu Items section.

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

b. Edit the check box text to use Fish taco to replace the placeholder text.

c. Format the new check box to link it to cell $N$25.

d. Position the Fish taco check box so that its box aligns with the Steak quesadilla box below it and with the Classic nachos box to its left.

9. Gabriel 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 A10, insert a Button (Form Control).

b. Assign the ClearForm macro to the button.

c. Edit the button to use Clear to replace the placeholder text.

d. Format the new button to change its height to 0.4" and the width to 1.3".

e. Position the Clear button to align with the top and bottom of the Save Form button on its right.

10. Before testing the new button, Gabriel asks you to change the text of the Save Form button so that it is more similar to the Clear button. Use the text Save as the caption of the command button.

11. Now that the Catering Orders worksheet is complete, Gabriel asks you to test it. Test the controls on the Catering Orders 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 button, and then unhide the Order Information worksheet to make sure the data is recorded in the worksheet.

c. Hide the Order Information worksheet, return to the Catering Orders worksheet, and then click the Clear button to clear the entries.

Table 1: Data for the Catering Orders Worksheet

Item to Enter | Data First Name (cell B4) | Ed Address (cell B5) | 251 Center St. City (cell B6) | Fort Worth State (cell B7) | TX Postal Code (cell B8) | 76008 Last Name (cell D4) | Burke Email (cell D5) | eburke@example.net Main Phone (cell D6) | 817-555-4599 Org Name (cell D7) | Burke Wedding Event Type (cell F3) | Personal Duration (cell C9) | 4 hours Menu Items (cell A12) | Classic nachos

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: Weekly Sales Worksheet

Final Figure 2: Catering Orders Worksheet