New Perspectives Excel 2019 | Module 12: SAM Project 1b FestivaPiedmont Riverfront Festival DEVELOP AN EXCEL APPLICATION GETTING STARTED ? Open the file NP_EX19_12b_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought4

Share With

New Perspectives Excel 2019 | Module 12: SAM Project 1b

FestivaPiedmont Riverfront Festival

DEVELOP AN EXCEL APPLICATION

GETTING STARTED

?

Open the file

NP_EX19_12b_

FirstLastName

_1.xlsm

, available for download from the

SAM website.

?

Save the file as

NP_EX19_12b_

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.

?

With the file

NP_EX19_12b_

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 add 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, and click OK.

PROJECT STEPS

1.

Kiara Banerjee is coordinating the volunteers for the Piedmont Riverfront Festival in

Piedmont, Virginia. She is developing an Excel workbook to track and record the

volunteers' information, including registration information. She asks for your help in

automating the workbook.

Go to the

Volunteer Overview

worksheet and then unprotect it so you can edit the

contents.

2.

Kiara wants to include an eye-catching title on the worksheet, which she plans to share

with the other festival coordinators.

Insert and format WordArt as follows:

a.

Insert WordArt using the

Fill: Brown, Accent color 4; Soft Bevel

style.

b.

Type

Festival Volunteers

as the worksheet title.

c.

Change the text fill of the WordArt to

Brown, Accent 4, Darker 25%

.

d.

Move the WordArt to row 1 so that it spans columns A:H.

3.

Kiara created a macro to insert a plain worksheet title, and then attached the macro to

the Title button. She no longer needs the button.

Delete the

Title

button in the range H4:H5.

4.

Kiara plans to print the

Volunteer Overview

worksheet before the first day of the festival.

She wants to use the Print Layout button to prepare the worksheet for printing.

Record a macro that sets up the worksheet for printing as follows:

a.

Record a macro named

Print_Layout

stored in the current workbook.

b.

With the macro recording, click the

File

tab on the ribbon, and then click

Print

.

c.

Change the page orientation to

Landscape Orientation

.

d.

Change the scaling setting to

Fit Sheet on One Page

.

e.

Return to the

Volunteer Overview

worksheet.

f.

Stop recording the macro.

5.

Kiara created the Print Layout button using a Rounded Rectangle shape. She wants to be

able to click the shape to run the Print_Layout macro.

Assign the

Print_Layout

macro to the Print Layout shape.

6.

Go to the

Volunteer Records

worksheet, where Kiara tracks volunteer assignments. She

and her staff usually enter the volunteer details directly on the worksheet. She wants to

make sure everyone enters the data correctly, especially the dates in the Date column.

The festival begins on May 28, 2021 and ends on May 31, 2021.

Create a data validation rule as follows:

a.

In the range C3:C16, create a data validation rule that accepts only

date

values

between

5/28/21

and

5/31/21

.

b.

Create an input message that uses

Volunteer Assignment Date

as the title and

the following text as the input message:

Enter the date of the volunteer assignment.

c.

Create a Stop style error alert that uses

Invalid Date

as the title and the following

text as the error message:

Enter a date between May 28 and May 31.

7.

Kiara wants to make sure all of the dates entered in the Date column are valid.

Identify and correct any invalid values as follows:

a.

In the range C3:C16, circle invalid data.

b.

Change the first invalid date to

5/28/21

and change the second invalid date to

5/30/21

to correct the errors.

8.

Go to the

Volunteer Registration

worksheet. Before the festival, Kiara and her staff will

register volunteers using the form on this worksheet. Kiara has created two macros to

automate this form, but they are not working correctly.

View and edit the macros as follows:

a.

Open the

Clear_Data

macro in the Visual Basic Editor. The macro should delete

the values in the range D4:D8, but specifies the range C3:C9.

b.

In the Clear_Data macro VBA code, change the statement between the "'Edit the

code below" and "'Edit the code above" comments to the following statement:

Range("D4:D8").Select

c.

Scroll down to display the code for the Add_Volunteer macro, the second macro

Kiara created. This macro calls, or runs, the Clear_Data macro at the end of the

code, so it should now work correctly. Save the code and then close the Visual

Basic Editor.

d.

Assign the

Clear_Data

macro to the Clear Data button so that Kiara and her staff

can use the button to clear the form.

e.

Use the Clear Data button to clear the form and test the macro.

9.

The Add_Volunteer macro should copy the data in the range D4:D8 on the

Volunteer

Registration

worksheet, go to the

Volunteer Records

worksheet, find the next blank row,

and then paste the copied data, transposing it to fit in a row instead of a column. Then

the macro should return to the

Volunteer Registration

worksheet and run the Clear_Data

macro, which deletes the data in the range D4:D8.

Kiara wants to assign the Add_Volunteer macro to a new button to make it easy to run

the macro. Create the button as follows:

a.

Insert a

Button (Form Control)

button in column D to the right of the Clear Data

button.

b.

Change the height to

0.3"

and the width to

1.5"

.

c.

Align the top and bottom of the new button with the top and bottom of the Clear

Data button.

d.

Edit the text to display

Add Volunteer

on the button.

e.

Format the button control to use

Rockwell 12 point

text, if necessary.

f.

Assign the

Add_Volunteer

macro to the new button.

10.

Insert the tutor registration data shown in Table 1, and then use the

Add Volunteer

button to run the Add_Volunteer macro. Verify that it added a record on the

Volunteer

Records

worksheet.

Table 1: Volunteer Registration Data

Volunteer Name

Emma Johnson

Assignment

Riverfront Park

Date

5/30/21

Station

First aid

Gift

Mug

Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM

website to submit your completed project.