New Perspectives Excel 365/2021 | Module 12: End of Module Project 1
New Perspectives Excel 365/2021 | Module 12: End of Module Project 1
|
2 |
Abernathy Academy
DEVELOP AN EXCEL APPLICATION
GETTING STARTED
Save the file
NP_EX365_2021_EOM12-1_
FirstLastName_1.xlsm
as
NP_EX365_2021_EOM12-1_
FirstLastName_2.xlsm
a. Edit the file name by changing “1” to “2”. b. If you do not see the
.xlsm file extension, do not type it. The file extension will be added for you automatically.
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:
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.
For Mac: If a dialog box about macros appears, click Enable Macros.
With the file
NP_EX365_2021_EOM12-1_
FirstLastName_2.xlsm
open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
Tarif Hajany is the admissions director at the Abernathy Academy, a private secondary school in Roswell, Georgia. He is developing an Excel workbook to track and record tuition payments from registered students, which he and his staff record weekly. He asks for your help in automating the workbook. Go to the
Payments worksheet and then unprotect it so that you can edit the contents.
Tarif wants to include a title at the top of the worksheet similar to the ones used in the rest of the workbook. Insert WordArt using the Fill: Gray, Accent color 1; Shadow style. Type
Payments by Account as the worksheet title. Change the font size to 28 point, and then move the WordArt to row 1 so that it spans columns C:F.
Tarif thought he would print the worksheet, but no longer plans to do so. Clear the print area from the worksheet.
Go to the
Entry Form worksheet. The Admissions staff will use this worksheet to enter tuition payments and summarize tuition information. Tarif wants to make it easy to record the payments and avoid errors. In cell C3, add a data validation list that accepts only the values in the range I3:I203.
In cell C4, create another data validation rule that allows date values greater than or equal to 10/7/2024. Add an input message to the cell with the title
Date of Payment and use
Enter a date of 10/7/2024 or later. as the input message. Add a Warning alert with the title
Invalid Date as use
Verify the payment date. as the error message.
Hide column I to avoid distracting users with the Account ID list.
After users enter the account ID, date, and payment amount, Tarif wants them to click the Enter Payment button to add a record to the Late_Payments table on the
Payments worksheet. Record a macro named
Enter_Payment stored in the current workbook. With the macro recording, perform the following steps:
· Go to the
Payments worksheet, and then click cell H4.
· Insert a table row above, click an empty cell, then reclick on cell H4.
· Go to the
Entry Form worksheet, and then copy the data in the range C3:C5.
· Return to the
Payments worksheet, click the Paste arrow, and then click the Transpose option.
· Select cell L1.
· Return to the
Entry Form worksheet, press ESC, and then click cell B15.
· Stop recording the macro.
Tarif planned to display a confirmation message after users add a payment record to the Late_Payments table. Edit the Enter_Payment macro in the Visual Basic window. After the line of code that selects cell B15 (Range(“B15”).Select), insert the following new lines of code, and then save and close the macro.
ActiveCell.FormulaR1C1 = “Payment information entered”
Range(“C3”).Select
Assign the Enter_Payment macro to the Enter Payment button (a rounded rectangle shape).
Enter the data found in Table 1 on the
Entry Form worksheet, and run the Enter Payment macro to test that the confirmation message has been added correctly.
Table 1: Data for the Range C3:C5
|
C |
|
|
3 |
AC-1027 |
|
4 |
10/7/2024 |
|
5 |
3100 |
Tarif created a macro named Clear that clears the data in the range C3:C5 as well as the confirmation message. He assigned the Clear macro to the Clear Entries button, and asks you to test the assigned macro. Click the Clear Entries button to run the Clear macro.
Tarif wants to display statistics about tuition payments in the range E3:G12. This information is stored in the Summary PivotTable on the
Payment Summary worksheet. In the merged cell E4, use the GETPIVOTDATA function to display the number of registered students from cell B9 in the Summary PivotTable on the
Payment Summary worksheet. In the merged cell G4, display the total amount of tuition billed from cell C9 of the Summary PivotTable. In the merged cell G8, display the total amount of tuition paid from cell D9 of the Summary PivotTable.
To prevent users from modifying the formulas, Tarif wants you to protect the
Entry Form worksheet. Unlock cells C3:C5 so that users can enter data in the range. Protect the worksheet without using a password.
Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
