Homework answers / question archive / Author: Note: Do not edit this sheet

Author: Note: Do not edit this sheet

Business

Share With

Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from th SAM website. New Perspectives Excel 2019 | Module 12: SAM Project 1b Piedmont Riverfront Festival DEVELOP AN EXCEL APPLICATION David Youmara Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. he file from the Piedmont Square Station Bike races Boat rides First aid Food booths Kid's corner Music stage Total AM Volunteers 15 8 5 12 10 8 58 Station Bike races Boat rides First aid Food booths Kid's corner Music stage Total AM Volunteers 10 7 7 10 10 6 50 PM Volunteers 12 10 8 20 10 5 65 Summary Total 27 18 13 32 20 13 123 Station Bike races Boat rides First aid Food booths Kid's corner Music stage Total Total 20 16 15 22 20 15 108 Station Bike races Boat rides First aid Food booths Kid's corner Music stage Total Capital Street PM Volunteers 10 9 8 12 10 9 58 Riverfront Par Summary Total Volunteers 92 67 50 83 60 47 399 Print Layout Title Riverfront Park AM Volunteers 21 15 10 14 10 8 78 PM Volunteers 24 18 12 15 10 11 90 Total 45 33 22 29 20 19 168 Volunteer Name Dawn Millman Felipe Gomez Taylor Blackburn Jen Saleska Cosmo Salazar Yee Njai Charlie Olneck Vicky Webster Chee Tang Heather Rhys Jeong Park Tracy Woodward Rosalina Garduno Maddie Ducharme Assignment Date Riverfront Park Piedmont Square Riverfront Park Capital Street Piedmont Square Capital Street Riverfront Park Piedmont Square Capital Street Piedmont Square Riverfront Park Riverfront Park Capital Street Piedmont Square Fri, May 28 Sat, May 29 Fri, May 28 Sat, May 29 Wed, July 28 Sun, May 30 Sun, May 30 Mon, May 31 Thu, May 20 Mon, May 31 Fri, May 28 Sat, May 29 Mon, May 31 Sat, May 29 Station Food booths Music stage Food booths Boat rides Bike races Kid's corner First aid Kid's corner Music stage Boat rides Bike races First aid Food booths Kid's corner Gift T-shirt Cap Cap Mug T-shirt T-shirt Mug Cap T-shirt T-shirt Cap Mug Cap T-shirt Piedmont Riverfront Festival Volunteer Registration Volunteer Name Assignment Date Station Gift Luciana Flores Riverfront Park Sat, May 29 Food booths T-shirt New Perspectives Excel 2019 | Module 12: SAM Project 1b Piedmont Riverfront Festival DEVELOP AN EXCEL APPLICATION GETTING STARTED • Open the file NP_EX19_12b_FirstLastName_1.xlsm, available for download from the SAM website. • Save the file as NP_EX19_12b_FirstLastName_2.xlsm by changing the “1” to a “2”. o • With the file NP_EX19_12b_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o • 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. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. 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. Kiara Banerjee is coordinating the volunteers for the Piedmont Riverfront Festival in Piedmont, Virginia. She is developing an Excel workbook to track and record the volunteers' information, including registration information. She asks for your help in automating the workbook. Go to the Volunteer Overview worksheet and then unprotect it so you can edit the contents. 2. Kiara wants to include an eye-catching title on the worksheet, which she plans to share with the other festival coordinators. Insert and format WordArt as follows: 3. a. Insert WordArt using the Fill: Brown, Accent color 4; Soft Bevel style. b. Type Festival Volunteers in the WordArt to make the worksheet title. c. Change the text fill of the WordArt to Brown, Accent 4, Darker 25%. d. Move the WordArt to row 1 so that it spans columns A:H. Kiara created a macro to insert a plain worksheet title, and then attached the macro to the Title button. She no longer needs the button. Delete the Title button in the range H4:H5. New Perspectives Excel 2019 | Module 12: SAM Project 1b 4. Kiara plans to print the Volunteer Overview worksheet before the first day of the festival. She wants to use the Print Layout button to prepare the worksheet for printing. Record a macro that sets up the worksheet for printing as follows: a. Record a macro named Print_Layout stored in the current workbook. b. With the macro recording, click the File tab on the ribbon, and then click Print. c. Change the page orientation to Landscape Orientation. d. Change the scaling setting to Fit Sheet on One Page. e. Return to the Volunteer Overview worksheet. f. Stop recording the macro. 5. Kiara created the Print Layout button using a Rounded Rectangle shape. She wants to be able to click the shape to run the Print_Layout macro. Assign the Print_Layout macro to the Print Layout shape. 6. Go to the Volunteer Records worksheet, where Kiara tracks volunteer assignments. She and her staff usually enter the volunteer details directly on the worksheet. She wants to make sure everyone enters the data correctly, especially the dates in the Date column. The festival begins on May 28, 2021 and ends on May 31, 2021. Create a data validation rule as follows: 7. 8. a. In the range C3:C16, create a data validation rule that accepts only date values between 5/28/21 and 5/31/21. b. Create an input message that uses Volunteer Assignment Date as the title and the following text as the input message: Enter the date of the volunteer assignment. c. Create a Stop style error alert that uses Invalid Date as the title and the following text as the error message: Enter a date between May 28 and May 31. Kiara wants to make sure all of the dates entered in the Date column are valid. Identify and correct any invalid values as follows: a. In the range C3:C16, circle invalid data. b. Change the first invalid date to 5/28/21 and change the second invalid date to 5/30/21 to correct the errors. Go to the Volunteer Registration worksheet. Before the festival, Kiara and her staff will register volunteers using the form on this worksheet. Kiara has created two macros to automate this form, but they are not working correctly. View and edit the macros as follows: a. Open the Clear_Data macro in the Visual Basic Editor. The macro should delete the values in the range D4:D8, but specifies the range C3:C9. b. In the Clear_Data macro VBA code, change the statement between the "'Edit the code below" and "'Edit the code above" comments to the following statement: Range("D4:D8").Select c. Scroll down to display the code for the Add_Volunteer macro, the second macro Kiara created. This macro calls, or runs, the Clear_Data macro at the end of the New Perspectives Excel 2019 | Module 12: SAM Project 1b code, so it should now work correctly. Save the code and then close the Visual Basic Editor. 9. 10. d. Assign the Clear_Data macro to the Clear Data button so that Kiara and her staff can use the button to clear the form. e. Use the Clear Data button to clear the form and test the macro. The Add_Volunteer macro should copy the data in the range D4:D8 on the Volunteer Registration worksheet, go to the Volunteer Records worksheet, find the next blank row, and then paste the copied data, transposing it to fit in a row instead of a column. Then the macro should return to the Volunteer Registration worksheet and run the Clear_Data macro, which deletes the data in the range D4:D8. Kiara wants to assign the Add_Volunteer macro to a new button to make it easy to run the macro. Create the button as follows: a. Insert a Button (Form Control) button in column D to the right of the Clear Data button. b. Change the height to 0.3" and the width to 1.5". c. Align the top of the new button with the top of the Clear Data button. d. Edit the text to display Add Volunteer on the button. e. Format the button control to use Rockwell 12 point text, if necessary. f. Assign the Add_Volunteer macro to the new button. Insert the tutor registration data shown in Table 1, and then use the Add Volunteer button to run the Add_Volunteer macro. Verify that it added a record on the Volunteer Records worksheet. Table 1: Volunteer Registration Data Volunteer Name Assignment Date Station Gift Emma Johnson Riverfront Park 5/30/21 First aid Mug 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. New Perspectives Excel 2019 | Module 12: SAM Project 1b Final Figure 1: Volunteer Overview Worksheet Final Figure 2: Volunteer Records Worksheet New Perspectives Excel 2019 | Module 12: SAM Project 1b Final Figure 3: Volunteer Registration Worksheet