Shelly Cashman Excel 365

Shelly Cashman Excel 365 | Module 11: End of Module Project 1 City of Oakham

CREATE A USER INTERFACE

September 15, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file SC_EX365_EOM11-1_FirstLastName_1.xlsm as SC_EX365_EOM11-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.

2. To complete this Project, you will also need the following files:

Support_EX365_EOM11-1_Oak.png

3. With the file SC_EX365_EOM11-1_FirstLastName_2.xlsm open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

4. Files downloaded from the 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:

a. 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.

5. To complete this project, you need to display the Developer tab. If this tab is not displayed, 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. Alicia Castillo is an office manager for the Oakham City Council in Oakham, Connecticut. She is using an Excel workbook to track responses to a questionnaire inviting residents to serve on a municipal committee. She asks for your help in automating the workbook and creating an effective user interface for other office employees. Go to the Questionnaire worksheet, which is a form the office staff can use to collect information from residents. Unprotect the worksheet using Oakham as the password.

2. The workbook should look the same as other files the city uses. Alicia 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 Arial Narrow as the body font. Customize the theme colors to use Turquoise, Accent 6 as the hyperlink color. Save the new settings as a custom theme, using Oakham as the theme name.

3. Alicia 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 named range Phone.

4. Alicia also asks you to add a fourth option to the "Which committee do you want to join?" section of the form. In cell E14, insert an Option Button (Form Control). Edit the option button text to use Walkways to replace the placeholder text. (Hint: To ensure that the option buttons work independently, place the new button completely within the group box when you first insert it.) Format the new option button to use 3-D shading, and to link to cell $I$25 if necessary. Align the new option button with the left side of the Riverways option button and with the top of the Greenspace option button.

5. Alicia wants you to arrange the other option buttons more attractively on the form. Distribute the four option buttons in the "How do you rate Oakham city services?" section horizontally.

6. Alicia asks you to format the group box containing the rating option buttons to match the Committees 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. Alicia 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. Alicia 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 of the Save button on its left, and then assign the ClearData macro to the Clear button.

9. Alicia created a macro named Save 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 Resident Information worksheet. Open the Save 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 Add Record button to enter the resident information shown in Table 1. Select Bicycling as the committee and Excellent as the rating. Select the City resident and Website check boxes (if necessary), and then click the Save button to run the Save macro.

Table 1: Form Data

10. Unhide the Resident Information worksheet to verify that it contains the data from the Questionnaire worksheet in row 3. Format the worksheet to make it more eye-catching by adding the image in the file Support_EX365_EOM11-1_Oak.png to the worksheet background. Hide the Resident Information worksheet to keep the data private.

11. Go to the Committees worksheet. First, Alicia wants you to provide a graphical way to compare membership for each committee. She also asks you to make the stacked column chart more attractive and easier to interpret. For the committee membership data (range C6:F9), use the Quick Analysis tool to add Line sparklines to the range G6:G9 to chart the Year 1 to Year 4 values for each committee. 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. Alicia also wants you to make it easy to view the committee 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 Members 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 website to submit your completed project.

The Resident Information worksheet has been intentionally hidden.

Final Figure 1: Questionnaire Worksheet

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Committees Worksheet

Need help with this project?