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

Subject:MS ExcelPrice:19.87 Bought3

Share With

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

?

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.

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

RHunter@example.qm.co

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.

Final Figure 1: Repair Quotes Worksheet

Final Figure 2: Service Information Worksheet

Final Figure 3: Customer Entry Worksheet – Rows 1:20