Developing an Excel Application

Excel – Tutorial 7 (Mini Project)

Developing an Excel Application

 

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 S. 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 downloaded from Blackboard, and then save the workbook as Youth Events in your CS245 folder that you created.

 

2. In the Documentation worksheet, enter your name and the date.

Receipt worksheet

3. In the Receipt worksheet, define names as follows:
   1. Create defined names from selection using the range A3:B11 to name all of the input cells.
   2. Change the defined name Address to Street_Address

 

• Select Address listed in the Registration Data List > Formulas > Defined Names > Select Define Name > New Name dialog box opens >Street_Address> ok
• Add the rest of the names to the New Name box to create defined names listed

 

16. 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.

 

• Select D2:E8> Formulas > Defined Names > Select Define Name > New Name dialog box opens >Youth_Info> ok
• Select A13:B16> Formulas > Defined Names > Select Define Name > New Name dialog box opens >Events> ok

Documentation Worksheet

4. In the Documentation worksheet, in the range B9:B19, paste the list of defined names.

 

• Go to the Document worksheet > Select cell B9 > Formulas > Defined Names >Click the down arrow in Use in Formula > Select paste names located at the bottom of list

Receipt Worksheet

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.

 

• Cell B3 > Data > Data Tools >Data Validation (select down arrow)> Data Validation
• Settings Tab: Date, greater than or equal to, 1/1/2017
• Input Message Tab: Checkmark, Registration Date, Enter the date on the registration form.
• Error Alert Tab: Checkmark, Stop, Invalid Registration Date, The registration date must be present and >= 1/1/2017
• OK

 

• Cell B4 > Data > Data Tools >Data Validation
• Settings Tab: Any Value
• Input Message Tab: Checkmark, Youth Name, Please enter the full name of the youth participant.
• Error Alert Tab: Checkmark, Stop
• OK

 

• Cell B5 > Data > Data Tools > Data Validation
• Settings Tab: List, =$A$14:$A$16 (select this group of cells)
• Input Message Tab: Checkmark, Event, Click the arrow to select the event.
• Error Alert Tab: Checkmark, Stop, Invalid Event, Invalid Event. Use the arrow to select the event.
• OK

 

• Cell B6 > Data > Data Tools > Data Validation
• Settings Tab: List, =$D$4:$D$8 (select this group of cells)
• Input Message Tab: Checkmark, Grade, Click the arrow to select the Grade.
• Error Alert Tab: Checkmark, Stop, Invalid Grade, Invalid Grade. Use the arrow to select the Grade.
• OK

 

• Cell B7 > Data > Data Tools > Data Validation
• Settings Tab: List, =$E$4:$E$8 (select this group of cells)
• Input Message Tab: Checkmark, Shirt Size, Click the arrow to select the Shirt Size.
• Error Alert Tab: Checkmark, Stop, Invalid Grade, Invalid Grade. Use the arrow to select the Grade.
• OK

 

• Cell (select cell B8:B11) > Data > Data Tools > Data Validation
• Settings Tab: Any Value
• Input Message Tab: Checkmark, Guardian contact information, Please enter the Guardian contact information.
• Error Alert Tab: Checkmark, Stop
• OK

 

 

 

 

 

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:

 

1. Cell A40: =Guardian
2. Cell B40: =Telephone
3. Cell C40: =Youth_Name
4. Cell D40: =Event
5. Cell E40: =Grade
6. Cell F40: =Shirt Size

 

 

8. Enter the following formulas in the specified cells to add information to the registration receipt:

 

1. Cell B27: = Guardian
2. Cell B28: =Street Address
3. Cell B29: =City_State_Zip
4. Cell B30: =Telephone
5. Cell E2S: = Registration_Date
6. Cell E27: =Youth_Name
7. Cell E28: =Event
8. Cell E29: =Grade
9. 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.

 

• Formulas > Function Library > Logical > IF > Logical Test: Event=A14, Value_IF_True – B14, Value_IF_False –IF(Event=A15,B15,B16)

 

=IF(Event=A14,B14,IF(Event=A15,B15,B16)) complete formula

 

11. Unlock the input cells on the Receipt worksheet so that the user can enter data only in the range B3:B11.

 

• Select Cells B3:B11 >Home > Cells > Format > Format Cells>Protection > uncheck Lock box or unprotect sheet

 

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.

 

• Documentation worksheet > Review > Changes > Protect Sheet
• Registration Data worksheet > Review > Changes > Protect Sheet

 

13. Add the Developer tab to the ribbon.

 

• Backstage View (File tab) > Options > Custom Ribbon > Main Tabs > Select Developer

 

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:

 

• View > Macros > Macros > Record Macro >PDFEvent, e, This Workbook, Created 12/7/2017. Save receipt area: range A18:F37 as a PDF file.

 

1. Make the Receipt worksheet the active sheet.
2. Select the range A18:F37, and then set the selected range as the print area.

 

• Page Layout > Page Setup > Print Area > Set Print Area

 

1. Open the Page Setup dialog box. On the Margins tab make sure that the print area is centered horizontally on the page.

 

• Page Layout > Page Setup > Margins > Custom Margins > Margins > Check Horizontally
• View > Macros > Macros > Stop Recording

 

1. Export the worksheet to create a PDF/XPS document with the filename Event Receipt saved in your Excel Tutorial 7 folder.

 

• Right Click Receipt tab > Select Move or Copy >new book> Select Create a Copy >OK
• Save As: Event Receipt.pdf

 

1. Close the PDF file.
2. Clear the print area.

 

Page Layout > Page Setup > Print Area >Clear Print Area

 

1. Open the Page Setup dialog box. On the Margins tab, uncheck the Horizontally check box.
2. In the Receipt worksheet, make cell A1 the active cell.

 

16. Test macro using the shortcut key you assigned to it.

 

17. Turn on cell protection for the Receipt worksheet.

 

18. Remove the Developer tab from the ribbon.

 

19. Save the workbook as Youth Events with Macros, a macro-enabled workbook, and then close the workbook.

 

20. Upload Workbooks unto Blackboard to receive credit.