Question
The Rockport Youth Center also runs half-day Winter Youth Events, Hip-Hop Dance, Secret Agent Day, and Zombietron Workshop, for boys and girls in grades 1
The Rockport Youth Center also runs half-day Winter Youth Events, Hip-Hop Dance, Secret Agent Day, and Zombietron Workshop, for boys and girls in grades 1 through 5. Stephen wants you to automate the registration process for the Winter Youth Events so it is similar to the process you created for the Winter Activities registration. Complete the following:
1. Open the Youth workbook located in the Excel7 > Review folder included with your Data Files, and then save the workbook as Youth Events in the location specified by your instructor.
2. In the Documentation worksheet, enter your name and the date.
3. In the Receipt worksheet, define names as follows:
a. Create defined names from selection using the range A3:B11 to name all of the input cells.
b. Change the defined name Address to Street_Address.
c. Use the Name box to create the defined name Youth_Info for the range D2:E8 and the defined name Events for the range A13:B16.
4. In the Documentation worksheet, in the range B9:B19, paste the list of defined names.
5. In the Receipt worksheet, in the range B3:B11, create the data validation rules, input messages, and error alerts shown in Figure 7-44.
Figure 7-44 Validation rules for the range B3:B11
6. In the range B3:B11, enter the data shown in Figure 7-45.
Figure 7-45 Registration data
7. Enter the following formulas for the transfer area in the specified cells using the defined names you created earlier:
a. Cell A40: =Guardian
b. Cell B40: =Telephone
c. Cell C40: =Youth_Name
d. Cell D40: =Event
e. Cell E40: =Grade
f. Cell F40: =Shirt_Size
8. Enter the following formulas in the specified cells to add information to the registration receipt:
a. Cell B27: =Guardian
b. Cell B28: =Street_Address
c. Cell B29: =City_State_Zip
d. Cell B30: =Telephone
e. Cell E25: =Registration_Date
f. Cell E27: =Youth_Name
g. Cell E28: =Event
h. Cell E29: =Grade
i. Cell E30: =Shirt_Size
9. Make sure that column E displays the date in the short date format and is wide enough to see the entire date.
10. In cell E36, enter a formula with a nested IF function. If the event is Hip-Hop Dance, the fee will be the value in cell B14; otherwise, if the event is Secret Agent Day, the fee will be the value in B15; otherwise, it will be the value in cell B16.
11. Unlock the input cells on the Receipt worksheet so that the user can enter data only in the range B3:B11.
12. Protect the Documentation and Registration Data worksheets so that the user cannot enter data. Do not use a password. The Receipt worksheet remains unprotected.
13. Add the Developer tab to the ribbon.
14. Save the workbook. If you have any trouble as you record the macros, you can close the workbook without saving, open the workbook that you saved, and start with Step 15.
15. Create a macro named PDFEvent with Ctrl+e as the shortcut key. Store the macro in the current workbook. Type Created 12/7/2017. Save receipt area, range A18:F37, as a PDF file. as the description. Record the following steps to create the PDFEvent macro:
a. Make the Receipt worksheet the active sheet.
b. Select the range A18:F37, and then set the selected range as the print area.
c. Open the Page Setup dialog box. On the Margins tab make sure that the print area is centered horizontally on the page.
d. Export the worksheet to create a PDF/XPS document with the filename Event Receipt saved in the location specified by your instructor.
e. Close the PDF file.
f. Clear the print area.
g. Open the Page Setup dialog box. On the Margins tab, uncheck the Horizontally check box.
h. In the Receipt worksheet, make cell A1 the active cell.
16. Create a macro named TransferData with Ctrl+d as the shortcut key. Store the macro in the current workbook. Type Created 12/7/2017. Copy values in the transfer area of the Receipt worksheet to the Registration Data worksheet. in the macro description. Record the following steps to create the TransferData macro:
a. Remove worksheet protection from the Registration Data worksheet.
b. Make the Receipt worksheet the active worksheet.
c. Select the range A40:F40 and then copy it to the Clipboard.
d. Go to the Registration Data worksheet.
e. Click cell A1, and then press the Ctrl+↓ keys to go to the last row with values.
f. On the Developer tab, in the Code group, click the Use Relative References button.
g. Move down one row.
h. On the Developer tab, in the Code group, click the Use Relative References button.
i. Paste the values you copied in the Registration Data worksheet.
j. Click cell A1.
k. Turn on worksheet protection for the Registration Data worksheet.
l. Go to the Receipt worksheet, and then make cell B3 the active cell.
17. Test each macro using the shortcut keys you assigned to it.
18. In the Receipt worksheet, create the following macro buttons:
a. For the PDFEvent macro, create a macro button over the range D10:D11 with the label Print PDF Receipt.
b. For the TransferData macro, create a macro button over the range D12:D13 with the label Transfer Data.
19. Turn on cell protection for the Receipt worksheet.
20. Test the PDFEvent and TransferData macro buttons.
21. Edit the PDFEvent macro. Scroll to the last lines of the macro and in the line with the command Range("A1").select, change A1 to B3.
22. Remove the Developer tab from the ribbon.
23. Save the workbook as Youth Events with Macros, a macro-enabled workbook, and then close the workbook.
Cell(s) Settings Input Message Error Alert Registration Date must be >=1/1/2017 Title: Registration Date Message: Enter the date on the registration form. Style: Stop Title: Invalid Registration Date Message: The registration date must be present and >=1/1/2017. B3 B4 Any value Title: Youth Name Message: Please enter the full name of the youth participant. Style: Stop Title: Invalid Event B5 Title: Event List Source (range A14:A16 in the Receipt select the Event. worksheet) Message: Click the arrow to Message: Use the arrow to select the Event. List Source (range D4:D8 in the Receipt worksheet) Title: Grade Style: Stop Title: Invalid Grade Message: Use the arrow to select the Grade. B6 Message: Click the arrow to select the Grade. Title: Shirt Size Style: Stop Title: Invalid Shirt Size Message: Use the arrow to select the Shirt Size. B7 List Source (range E4:E8 in the Receipt worksheet) Message: Click the arrow to select the Shirt Size. B8:B11 Any value Title: Guardian contact information Message: Please enter the Guardian contact information.
Step by Step Solution
3.51 Rating (144 Votes )
There are 3 Steps involved in it
Step: 1
Instruction 1 Open the Youth workbook located in the Excel7 Review folder in the Student Data Files ...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started