Homework answers / question archive / Shelly Cashman Excel 2019 | Module 11: End of Module Project 1 Menlo Park Eye Center CREATE A USER INTERFACE   GETTING STARTED Open the file SC_EX19_EOM11-1_FirstLastName_1

Shelly Cashman Excel 2019 | Module 11: End of Module Project 1 Menlo Park Eye Center CREATE A USER INTERFACE   GETTING STARTED Open the file SC_EX19_EOM11-1_FirstLastName_1

MS Excel

Share With

Shelly Cashman Excel 2019 | Module 11: End of Module Project 1

Menlo Park Eye Center

CREATE A USER INTERFACE

• GETTING STARTED

• Open the file SC_EX19_EOM11-1_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_EOM11-1_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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_EOM11-1_Logo.png
• With the file SC_EX19_EOM11-1_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 display 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.
  
  Click the OK button to close the Excel Options dialog box and add the Developer tab to the ribbon.

• PROJECT STEPS

1. Leila Ardavan is the office manager at Menlo Park Eye Center, an optometry clinic in Menlo Park, California. She is using an Excel workbook to track patient responses to a survey, and asks for your help in automating the workbook and creating an effective user interface for other office employees.
   
   Go to the Patient Survey worksheet, which is a form the office staff can use to collect information from patients. Unprotect the worksheet using Menlo20 as the password.
2. The workbook should look the same as other files Menlo Park Eye Center uses. Leila asks you to change the colors and fonts and save them as a theme so that it can be easily applied to other workbooks.
   Customize the theme fonts to use Arial as the heading font and Calisto MT as the body font. Customize the theme colors to use Dark Purple, Background 2 as the hyperlink color. Save the new settings as a custom theme, using Menlo as the theme name.
3. Leila asks you to insert a missing formula in the top part of the form. In cell D8, enter a formula without using a function that displays the patient's phone number from the cell named Phone (cell G24).
4. Leila also asks you to add a fourth option to the "Which eye doctor do you usually see?" section of the form.
   In cell E14, insert an Option Button (Form Control). Edit the option button text to use I don't know to replace the placeholder text. Format the new option button to use 3-D shading and to link to cell $I$25. Align the new option button with the left side of the Dr. Kam Vinya option button and with the top of the Dr. Felipe Cruz option button.
5. Leila wants to arrange the other option buttons more attractively on the form. Distribute the four option buttons in the "How do you rate your visit to Menlo Park Eye Center?" section horizontally.
6. Leila asks you to format the group box containing the rating option buttons to match the Doctor group box. Edit the group box text to use Rating to replace the placeholder text. Format the group box to use 3-D shading.
7. Leila needs to add another check box to the form.
   In cell H16, insert a Check Box (Form Control). Edit the check box text to use TV to replace the placeholder text. Format the new check box to link it to cell $N$24 and to use 3-D shading. Position the TV check box so that its box aligns with the top of the Social media check box to its left.
8. Leila created a macro in Visual Basic named ClearData that clears the form for a new patient. She wants users to click a button to run the macro.
   In the range H18:H19, insert a Button (Form Control). Resize the new button to a height of 0.3" and a width of 1". Change the font to 11-point Arial regular. Edit the button text to use Clear to replace the placeholder text. Position the Clear button to align with the top and bottom of the Save button on its left, and then assign the ClearData macro to the Clear button.
9. Leila created a macro named SaveData and assigned it to the Save button. However, the macro code contains an error, which she asks you to correct. The macro should select the entries in the range A24:N24, and then copy them to the hidden Patient Information worksheet. Open the SaveData macro in the Visual Basic editor. Change the first statement (Range("A24:H24").Select) to reference A24:N24 as the range. Save and close the macro code.
   Use the Enter Data button to enter the patient information shown in Table 1. Select Dr. Felipe Cruz as the doctor and Excellent as the rating. Select the Other patient and Web search check boxes (if necessary), and then click the Save button to run the SaveData macro.

10. Unhide the Patient Information worksheet to verify that it contains the data from the Patient Survey worksheet. Format the worksheet to make it more eye-catching by adding the image in the file Support_EX19_EOM11-1_Logo.png to the worksheet background. Hide the Patient Information worksheet to keep the data private.
11. Go to the Patient Visits worksheet. First, Leila wants to provide a graphical way to compare patient visits by doctor. She also wants to make the stacked column chart more attractive and easier to interpret.
    For the patient visit data (range C6:F9), use the Quick Analysis tool to add Line sparklines to the range G6:G9 to chart the Qtr 1 to Qtr 4 values for each doctor. For the stacked column chart, add a legend to the bottom of the chart, and then add an Offset: Center outer shadow to the plot area.
12. Leila also wants to make it easy to view the patient visit data and stacked column chart at the same time.
    Zoom the worksheet to 80%, and then save the worksheet settings as a custom view using Visits as the name of the view.

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.