DEVELOP AN EXCEL APPLICATION

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.

image9.png

image6.png

image7.png

image8.png

CustomEssayMasters

Ready To Get Started

The Exertio is a Premium WordPress Theme, you can create your own market place website using this theme. It allows you to get a commission for hiring a freelancer or for each service sold.