Homework answers / question archive / Open the file NP_EX19_12a_FirstLastName_1

Open the file NP_EX19_12a_FirstLastName_1

MS Excel

Share With

• Open the file NP_EX19_12a_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as NP_EX19_12a_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_12a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• To complete this project, you need to add the Developer tab. If this tab does not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the Developer check box, and click OK.

• PROJECT STEPS

1. Luis Palomba is in charge of community relations for Penta Insurance, a health insurance agency in Minneapolis, Minnesota. Luis is coordinating a program that sends tutors from Penta Insurance to area elementary schools. He is developing an Excel workbook to track and record the tutoring information, and needs to complete it before the upcoming Volunteer Fair. He asks for your help in automating the workbook.
   Go to the Tutor Summary worksheet and then unprotect it so you can edit the contents.
2. Luis wants to include an eye-catching title on the worksheet, which he shares with others at Penta Insurance.
   Insert and format WordArt as follows:
   1. Insert WordArt using the Fill: Dark Red, Accent color 1; Shadow style.
   2. Type Penta Tutoring Program in the WordArt to make the worksheet title.
   3. Change the text fill of the WordArt to Green, Accent 6, Darker 50%.
   4. Move the WordArt to row 1 so that it fits within columns A:H.
3. Luis created a macro to insert a plain worksheet title, and then attached the macro to the Insert Title button. He no longer needs the button.
   Delete the Insert Title button in the range H5:H6.
4. Luis plans to print the Tutor Summary worksheet before he visits tutors in the schools. He wants to use the Print Setup button to prepare the worksheet for printing.
   Record a macro that sets up the worksheet for printing as follows:
   1. Record a macro named Print_Setup stored in the current workbook.
   2. With the macro recording, click the File tab on the ribbon, and then click Print.
   3. Change the page orientation to Landscape Orientation.
   4. Change the scaling setting to Fit Sheet on One Page.
   5. Return to the Tutor Summary worksheet.
   6. Stop recording the macro.
5. Luis created the Print Setup button using a Rounded Rectangle shape. He wants to be able to click the shape to run the Print_Setup macro.
   Assign the Print_Setup macro to the Print Setup shape.
6. Go to the Tutor Records worksheet, where Luis tracks tutor assignments. He and his staff usually enter the tutoring details directly on the worksheet. He wants to make sure everyone enters the tutoring data correctly, especially the dates in the Tutoring Start Date column. The tutoring program begins on March 22, 2021 and ends on May 14, 2021.
   Create a data validation rule as follows:
   1. In the range C5:C18, create a data validation rule that accepts only date values between 3/22/21 and 5/14/21.
   2. Create an input message that uses Tutoring Start Date as the title and the following text as the input message:
      Enter the date to start tutoring.
   3. Create a Stop style error alert that uses Invalid Date as the title and the following text as the error message:
      Enter a date between March 22 and May 14.
7. Luis wants to make sure all of the dates entered in the Tutoring State Date column are valid.
   Identify and correct any invalid values as follows:
   1. In the range C5:C18, circle invalid data.
   2. Change the first invalid date to 3/24/21 and change the second invalid date to 3/26/21 to correct the errors.
8. Go to the Tutor Registration worksheet. During the Volunteer Fair, Luis and his staff will enter tutor information using the form on this worksheet. Luis has created two macros to automate this form, but they are not working correctly.
   View and edit the macros as follows:
   1. Open the Clear_Form macro in the Visual Basic Editor. The macro should delete the values in the range C4:C8, but specifies the range C3:C7.
   2. In the Clear_Form macro VBA code, change the statement between the "'Edit the code below" and "'Edit the code above" comments to the following statement:
      Range("C4:C8").Select
   3. Scroll down to display the code for the Add_Tutor macro, the second macro Luis created. This macro calls, or runs, the Clear_Form macro at the end of the code, so it should now work correctly. Save the code and then close the Visual Basic Editor.
   4. Assign the Clear_Form macro to the Clear Form button so that Luis and his staff can use the button to clear the form.
   5. Use the Clear Form button to clear the form and test the macro.
9. The Add_Tutor macro should copy the data in the range C4:C8 on the Tutor Registration worksheet, go to the Tutor Records worksheet, find the next blank row, and then paste the copied data, transposing it to fit in a row instead of a column. Then the macro should return to the Tutor Registration worksheet and run the Clear_Form macro, which deletes the data in the range C4:C8.
   Luis wants to assign the Add_Tutor macro to a new button to make it easy to run the macro. Create the button as follows:
   1. Insert a Button (Form Control) button in column C to the right of the Clear Form button.
   2. Change the height to 0.3" and the width to 1.8".
   3. Align the top of the new button with the top of the Clear Form button.
   4. Edit the text to display Add Tutor on the button.
   5. Format the button control to use Calibri 11 point text if necessary.
   6. Assign the Add_Tutor macro to the new button.
10. First insert the tutor registration data shown in Table 1. Then use the Add Tutor button to run the Add_Tutor macro, and then verify that it added a record on the Tutor Records worksheet.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.