联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-21:00
  • 微信:codinghelp

您当前位置:首页 >> CS作业CS作业

日期:2018-10-07 09:49

A1 Rockport Youth Centre Registration1

An Excel Application

The Rockport Youth Centre 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 (the volunteer coordinator)

wants you to automate the registration process for the Winter Youth Events.

Read all the instructions first. There are links at the end that may help with some new features.

Complete the following:

1. Open the Youth.xlsx workbook located in the A1 Dropbox on MyLearningSpace, and then save

the workbook as Youth Events.xlsx on your computer in a folder you can find easily.

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

Tip: Press CTRL+; on Windows to automatically place the current date into

a selected (active) cell.

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 (click the link to be shown a demonstration of this technique).

b. Change the defined name Address to Street_Address.

c. Use the Name box to create the defined name Youth_lnfo for the range D2:E8 and the

defined name Events for the range A13:B16.

4. In the Receipt worksheet, in the range B3:B11, create the data validation rules, input messages,

and error alerts shown in Table 1.

Table 1: Validation rules for the range B3:B11

Cell(s) Settings Input Message Error Alert

B3 Registration Date must

be >= 1/1/2019

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/2019

B4 Any value Title: Youth Name

Message: Please enter the full

name of the youth participant.

B5 List

Source: range A14:A16

in the Receipt

worksheet

Title: Event

Message: Click the arrow to

select the Event.

Style: Stop

Title: Invalid Event

Message: Use the arrow to select the

Event.

B6 List

Source: range D4:D8 in

the Receipt worksheet

Title: Grade

Message: Click the arrow to

select the Grade.

Style: Stop

Title: Invalid Grade

Message: Use the arrow to select the

Grade.

B7 List

Source: E4:E8 in the

Receipt Worksheet

Title: Shirt Size Style: Stop

Title: Invalid Shirt Size

Message: Use the arrow to select the

shirt size.


1 This application is based on an exercise in New Perspectives Microsoft Office 265 Excel 2016 by Parsons, Oja,

Carey, and Desjardin. Published by Cengage Learning. ?2017 Cengage Learning. Used with permission.

2

5. In the range B3:B11, enter the data shown here:

Registration Date 10/1/2019

Youth Name Henry Boardman

Event Zombietron Workshop

Grade G5

Shirt Size L

Guardian Sharon Boardman

Address 311 Walnut Ave.

City Prov. Postal Code Rockport, ON N2K 3C6

Telephone 818-555-1234

6. Enter the following formulas for the transfer area in the specified cells using the defined you

created earlier. You must include the equal sign (=) or it won’t work:

a. Cell A40: =Guardian

b. Cell B40: =Telephone

c. Cell C40: =Youth_Name

d. Cell 040: =Event

e. Cell E40: =Grade

7. 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_Prov._Postal_Code

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

8. Make sure that column E displays the date in the short date format and is wide enough to entire

date.

9. Protect the Documentation and Registration Data worksheets so the user cannot enter any

data. Do not use a password. The Receipt worksheet remains unprotected.

Tip: To protect a worksheet, click the Review tab at the top of the worksheet, and click Protect Sheet.

You need to do this for both worksheets.

10. Save the workbook. If you have any trouble as you record the macros in the next steps, you can

close the workbook without saving, open the workbook that you saved, and start with Step 11.

11. Record a macro named PDFEvent with Ctrl+e as the shortcut key. Store the macro in the

workbook. Type Created 20/9/2018. 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.

3

b. Select the range A18:F37, then click the Page Layout tab, then click Print Area, then click Set

Print Area.

Tip: This feature lets you set a selected range to print, instead of printing an entire worksheet.

c. Click Page Layout > Margins > Custom Margins. 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.

Tip: To do this, click the File tab, then click Export, then click Create PDF/XPS.

e. Close the PDF file if one is opened in your PDF reader program.

f. Clear the print area (the area that will be printed or written to the PDF) by clicking the Page

Layout tab, click Print Area, and then click Clear Print Area.

g. In the Receipt worksheet, make cell A1 the active cell.

12. Record a new macro named TransferData with Ctrl+d as the shortcut key. Store the macro in

the current workbook. Type Created 20/9/2018. 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 by clicking Review > Unprotect

Sheet.

b. Make the Receipt worksheet the active worksheet.

c. Select the range A40:F40 and then copy it to the Clipboard (just use the copy command which will

copy the selected data to the Windows Clipboard).

d. Go to the Registration Data worksheet.

4

e. Click cell A1, and then press the Ctrl+? keys (press CTRL and the down arrow key at the same time) 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.

13. Test each macro using the shortcut keys you assigned to it.

14. In the Receipt worksheet, create the following macro buttons:

a. For the PDFEvent macro, create a macro button over the range D10:E11 with the label

Print PDF Receipt.

b. For the TransferData macro, create a macro button over the range D12:E13 with the

label Transfer Data.

15. Protect the Receipt worksheet.

16. Test the PDFEvent and TansferData macro buttons.

17. Edit the PDFEvent macro by going into the VB Editor. Scroll to the last lines of the macro and in

the line with the command Range("A1") change A1 to B3.

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

the workbook. Make sure to replace user1234 with your Laurier user name.

For example, my completed file would be called Youth Events with Macros hend4830.xlsm.

WARNING: IF YOU DO NOT CHANGE THE FILE TYPE TO MACRO ENABLED WORKBOOK YOU WILL LOSE

YOUR MACROS!

References

Data Validation from Lab 1

Apply data validation to cells (Microsoft Office Support)

Create Range Names from Selection (Microsoft Office Support)


版权所有:编程辅导网 2021 All Rights Reserved 联系方式:QQ:99515681 微信:codinghelp 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。 站长地图

python代写
微信客服:codinghelp