Excel 2016 Module 11 SAM Project

This practical assignment focuses on completing the Shelly Cashman Excel 2016 Module 11 SAM Project 1a. Students are tasked with using various Excel features, including VBA macros, merging workbooks, adding WordArt and background images, working with form controls (option buttons, checkboxes, group boxes), using the INDEX function, and writing VBA code. The project involves manipulating data, adding comments, protecting worksheets, and using ActiveX controls. The assignment culminates in testing the completed workbook by entering customer information and using a clear customer info button. The solution provides step-by-step instructions to guide students through the process, ensuring they understand the application of different Excel functionalities.

Shelly Cashman Excel 2016 | Module 11: SAM Project 1a
Ricky Laine’s Masonry
USER INTERFACES, VBA, AND COLLABORATION FEATURES IN EXCEL
GETTING STARTED
 Open the file SC_EX16_11a_FirstLastName_1.xlsm, available for download
from the SAM website.
 Save the file as SC_EX16_11a_FirstLastName_2.xlsm by changing the “1” to
a “2”.
o 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 SC_EX16_11a_FirstLastName_2.xlsm still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new
copy from the SAM website.
 You will also need to download the following support files for this project:
o Support_SC_EX16_11a_AaronE.xlsm
o Support_SC_EX16_11a_Mason.jpg
NOTE: Do not open the support files as you may not be able to merge them.
 If you see a Message Bar with a security warning at the top of the Excel window,
click the Enable Content button in the Message Bar to enable the macros
contained in the file.
 To complete this project you will need to display the Developer tab. To add this
tab to the Excel ribbon, click the File tab to open Backstage View and then click
the Options button. In the Excel Options dialog box, click the Customize
Ribbon option and click the Developer check box. Click the OK button to close
the Excel Options dialog box and confirm the Developer tab appears in the
Excel Ribbon.
 To complete this project you will also need to display the Compare and Merge
Workbooks button to the Quick Access toolbar. Click the Customize Quick
Access Toolbar dropdown and select the More Commands… option. Select
the Commands not in the Ribbon option from the Choose commands from
list. Find the Compare and Merge Workbooks… option on the command list,
select it, and then click the Add button to add the Form command to the Quick
Access Toolbar. Click the OK button to close the Excel Options dialog box and
confirm that the Compare and Merge Workbooks option appears in the Quick
Access Toolbar.
PROJECT STEPS
1. Fernanda Laine works for her father Ricardo at Ricky Laine’s Masonry, doing
whatever needs to be done around the office. She is completing an Excel
workbook that was left incomplete by a former employee.

Shelly Cashman Excel 2016 | Module 11: SAM Project 1a
Switch to the Repair Quotes worksheet. Merge the changes made in the
Support_SC_EX16_11a_AaronE.xlsm workbook into
SC_EX16_11a_FirstLastName_2.xlsm. Using the Select Changes to Accept or
Reject dialog box, review all changes in the range A1:D13 of the Repair Quotes
worksheet as follows:
a. Accept the changes made in cells B5, B6, C8, and A13.
b. Reject the changes made in cells C7, D9, and B10.
Turn off workbook sharing.
2. Go to the merged cell A2 and edit the comment to read: Final quotes are
based on site inspection, not on the estimates provided in this table.
(including the period). Remove any other text that appears in the comment,
including (if necessary) your name.
3. Go to cell D11 and add the following comment: Indicate that rebuild may be
cheaper than repair, depending on the size of the job. (including the
period) to the cell. Remove any other text that appears in the comment,
including (if necessary) your name.
4. Use WordArt to add a watermark to the Repair Quotes worksheet as described
below:
a. Use the Fill - Black, Text 1, Shadow option for the watermark. (Hint:
Depending on your version of Office, the WordArt option may appear as
Fill: Black, Text color 1; Shadow.)
b. Enter the text Draft Pricing into the WordArt.
c. Reposition and resize the WordArt so that the upper left corner is located
in cell A6 and the lower right corner is located in cell E10.
d. Set the transparency of the WordArt text to 90%.
5. Go to the Service Information worksheet. Add a background image to the
worksheet, using the support file Support_SC_EX16_11a_Mason.jpg.
6. Fernanda wants to make sure that no unauthorized edits can be made to this
worksheet.
Protect the Service Information worksheet with the default settings, using
L@ine as the password.
7. The workbook includes a customer entry sheet, but several steps were left
unfinished.
Go to the Customer Entry worksheet. Select the option button labeled Option
Button 22 and then use the text Quote to replace the placeholder text.
8. Add an Option Button (Form Control) to the worksheet, using Final Figure 3
as a guide. Update the Option Button form control as identified below:
a. Use the text Scheduling to replace the placeholder text.
b. Confirm that the option button has a cell link to cell $J$26.
c. If necessary, resize and reposition the check box control so that the
control text is completely visible and the check box control is located
within cell F8.

Shelly Cashman Excel 2016 | Module 11: SAM Project 1a
9. Select the five option buttons in the range B9:E11. Align the option buttons
using Align Top formatting, distribute the buttons using the Distribute
Horizontally option, and then reposition them within the range B10:E10.
10. Add a Group Box (Form Control) in the range A9:E11 that completely
encloses the five option buttons in the range B10:E10. Make sure that the
Group Box form control completely encloses the five option buttons’ text fields,
which may be wider than the text displayed for each button.
11. Add a Check Box (Form Control) into the worksheet, using Final Figure 3 as a
guide. Update the Check Box form control as identified below:
a. Use the text Chimney to replace the placeholder text.
b. If necessary, resize and reposition the check box form control so that the
control text is completely visible and the check box form control is located
within cell C14.
c. Format the Check Box form control so that it has a cell link to cell $N$25.
12. In cell C15, format the Check Box form control with the label Facade so that it
has a cell link to cell $Q$25.
13. In cell J25, create a formula using the INDEX function to return the value from
the named range Request_Type (which represents the range A29:A31) based
on the value in cell J26.
14. Now that the workbook is almost complete, Fernanda wants to test it by adding
a record to the mailing list.
a. Use the button labeled “Enter Customer Information” to enter the
information shown in bold in Table 1 below:
Table 1: Customer Information
Field Value Field Value
First
Name
Rosie Last Name Hunter
Addres
s
9 Mill
Road
Email [e-mail]
m
City Alban
y
Main Phone
Number
518-555-1005
State New
York
Alternative
Phone
Number
518-555-0715
Postal
Code
12201

Shelly Cashman Excel 2016 | Module 11: SAM Project 1a
b. Select Inspection as the Request Type option. (Hint: You will need to
manually select this option—you will not be prompted by message boxes.)
c. Select Repair as the Work Request option.
d. Select the Stairs, Walkway, Patio, and Wall/Terrace check boxes as
the Job Type.
e. Confirm that your information is complete, and then click the button
labeled “Save Customer Information”.
(Hint: Cell H2 will be selected in the Customer Entry worksheet if the macro has
run correctly.)
15. To make the worksheet look neater, Fernanda wants to remove the Group Box
borders.
Open the Immediate window in the Visual Basic Editor, and run the following
code:
activesheet.groupboxes.visible=false
(Hint: For this code to work, the Customer Entry worksheet will need to be the
active worksheet.)
16. With the Customer Entry worksheet active, turn on Design Mode. Add a
Command Button (ActiveX Control) to the worksheet, and resize and
reposition the button so that it is completely within cell D2. Update the button
as described below:
a. Use the text Clear Customer Info to replace the placeholder text.
b. View the code associated with this button. Enter the following VBA code
between the “Private Sub CommandButton3_Click()” and “End Sub” lines:
Range(“B4:B8”).ClearContents
Range(“D4:D7”).ClearContents
Range(“J26:K26”).ClearContents
Range(“L25:S25”).ClearContents
(Hint: There are no spaces before or after the parentheses in the code.)
c. Confirm that your VBA code is correct by switching back to the Customer
Entry worksheet, turning off Design Mode, and clicking the button labeled
“Clear Customer Info”. The ranges B4:B8 and D4:D7 should be blank and
none of the request type, work request, or job type form controls should
be checked.
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.

Shelly Cashman Excel 2016 | Module 11: SAM Project 1a
Final Figure 1: Repair Quotes Worksheet
Final Figure 2: Service Information Worksheet

Shelly Cashman Excel 2016 | Module 11: SAM Project 1a
Final Figure 3: Customer Entry Worksheet – Rows 1:20