Shelly Cashman Excel 2016 | Module 11: SAM Project 1b Heirloom Woodworking USER INTERFACES, VBA, AND COLLABORATION FEATURES IN EXCEL GETTING STARTED ? Open the file SC_EX16_11b_ FirstLastName _1

Subject:MS ExcelPrice: Bought3

Share With

Shelly Cashman Excel 2016 | Module 11: SAM Project 1b

Heirloom Woodworking

USER INTERFACES, VBA, AND COLLABORATION FEATURES IN EXCEL

GETTING STARTED

?

Open the file

SC_EX16_11b_

FirstLastName

_1.xlsm

, available for download

from the SAM website.

?

Save the file as

SC_EX16_11b_

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_11b_

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_11b_Wood.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 that the Developer tab appears in the

Excel Ribbon.

PROJECT STEPS

1.

Brittany Keele works part-time at Heirloom Woodworking, a custom

woodworking shop in Irving, Texas. She is completing an Excel workbook that

one of the carpenters started but has not had time to finish.

Switch to the

Repair Quotes

worksheet. Go to cell D5 and add the following

comment:

Cabinetry can also be restored and rebuilt.

(including the

period) to the cell. Remove any other text that appears in the comment,

including (if necessary) your name.

2.

Use WordArt to add a watermark to the

Repair Quotes

worksheet as follows:

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 for Review

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

d.

Set the transparency of the WordArt text to

90%

.

3.

Go to the

Service Information

worksheet. Add a background image to the

worksheet, using the support file

Support_SC_EX16_11b_Wood.jpg

.

4.

Brittany wants to make sure that no unauthorized edits can be made to this

worksheet.

Protect the

Service Information

worksheet with the default settings, using

Kee!

e

as the password.

5.

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

Design

to replace the placeholder text.

6.

Add an

Option Button (Form Control)

to cell F8, 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.

7.

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.

8.

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.

9.

Add a

Check Box (Form Control)

to cell C14, using Final Figure 3 as a guide.

Update the Check Box form control as identified below:

a.

Use the text

Custom

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

.

10.

In cell C15, format the Check Box form control with the label Repair so that it

has a cell link to cell

$Q$25

. Use

Paneling

to replace the Repair label.

11.

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

.

12.

Now that the workbook is almost complete, Brittany 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

Ann

Last Name

West

Address

35 Laredo Drive

Email

AnnWest@mail.cengage.co

m

City

Irving

Main Phone

Number

214-555-1409

State

Texas

Alternative

Phone

Number

214-555-2188

Postal

Code

75014

b.

Select

Estimate

as the Request Type

option. (

Hint:

You will need to

manually select this option—you will not be prompted by message boxes.)

c.

Select

Custom Design

as the Work Request

option.

d.

Select the

Cabinets

and

Interior trim

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

13.

To make the worksheet look neater, Brittany 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.)

14.

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 completely covers the darker gold area within

cell D2. Update the button as follows:

a.

Use the text

Clear Customer Data

to replace the placeholder text.

b.

Use the Properties window to change the font to

Calibri

.

c.

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

d.

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 Data”. 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