Shelly Cashman Excel 2013 Chapter 10: SAM Project 1a Xiaobin Feng 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Shelly Cashman Excel 2013 Chapter 10: SAM Project 1a

Xiaobin Feng

1. 

Go to the Event Schedule – 2018 worksheet and use the password J@Nuary18 to

unprotect the worksheet.

2. Select cell A4 and use a Data Form to enter a new record shown (using the

information shown in Figure 4 in the Assignment file) into the Event Schedule -

2018 worksheet. The Event field value is intentionally left blank.

3. In cell B4, enter a formula using the PROPER function that converts the text in

cell C4 to proper case. Copy the formula into the range B5:B11. Adjust the width

of column B to best fit the content it contains and then hide column C.

4. Select cell J7. Make sure the Use Relative References option (in the Code section

of the Developer tab) is active and then begin recording a macro as described

below:

a. Set the name of the Macro to Event_Cleanup.

b. Set the shortcut key for the macro to Ctrl+Shift+L (by pressing the Shift and L

keys at the same time).

c. Store the macro in the current workbook.

d. Set the description of the macro to This macro splits the Event-Location entry

into a Building and a Room entry. (including the period).

e. With the macro recording, enter the formula =SEARCH("-",G7) into cell J7.

f. Move to cell I7 and enter the formula =RIGHT(G7,3) into the cell.

g. Copy then paste the Value of cell I7 into cell I7 so that the cell contains just a

number, rather than a formula. (Hint: Ignore the error about the number being

stored as text.)

h. Move to cell H7 and enter the formula =LEFT(G7, J7-1) into the cell.

i. Copy and then paste the Value of cell H7 into cell H7 so that the cell contains a

value, rather than a formula.

j. Stop recording the macro.

k. Assign the macro to the macro button labeled Entry Cleanup.

l. Confirm the macro works by selecting the cell J8 and running the

Entry_Cleanup macro (either using the macro button or the shortcut command).

m. Select cell J10 and run the Entry_Cleanup macro again to clean up another

entry in the Event Schedule - 2018 worksheet.

5. 

Go to the Center Signup Form worksheet. Add an Option Button (Form Control)

to the worksheet using Figure 5 in the Assignment file as a guide. (Hint: The

button should be located in the range D18:E19.)

a. Edit the text in the control to read Remove from List.

b. If necessary, resize and reposition the control so that the control text is

completely visible and completely within the Group Box 9 control.

6. Select the option button labeled Option Button 8 and then edit the text to read

U.S. Mail using Figure 5 in the Assignment file as a guide.

7. Select the four option buttons in the range F9:G15. Align the option buttons

using the Left option and then vertically distribute the buttons.

8. 

Add a Group Box (Form Control) that completely encloses the four option

buttons in the range F9:G15 using Figure 5 in the Assignment file as a guide.

(Hint: Your Group Box control may have a different label than the one shown in

Figure 5.) Make sure that the Group Box control completely encloses the four

option buttons’ text fields, which may be wider than the text displayed for each

button.

9. 

Format the option button control labeled Email, so that it has a cell link to

$K$45.

10. 

Add a Check Box (Form Control) into the worksheet using Figure 5 in the

Assignment file as a guide. (Hint: The Check box control should be located in the

range D24:E24.) Update the Check Box Control as identified below:

a. Edit the text in the control to read Other.

b. 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 the range

D24:E24.

c. Format the Check Box control so that it has a cell link of $R$44

11. 

In cell L44, use the INDEX function to return the value from the named range

Request_Type (which represents the range V44:V46) based on value in cell L45.

12. 

Add a record to the Center Mailing List by completing the following steps:

a. Use the button labeled Click to Enter Contact Information to enter the

information shown in Table 1 in the Assignment file.

b. Select the Daytime Phone for the Contact By option. (Hint: You will need to

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

c. If necessary, select Add to List for the Request option.

d. Select the Writing Resumes, Networking Skills, and Job Search Tools check

boxes.

e. Confirm that your worksheet matches Figure 6 in the Assignment file and then

click the button labeled Click to Submit Information.

13. 

Switch to the Visual Basic Editor view (Hint: Click the View Code button in the

Controls section of the Developer tab). Open the immediate window and run the

following code:

Sheets(“Center Signup Form”).groupboxes.visible=false

Then, switch back to the Center Signup Form worksheet and confirm that the

Group Box borders are no longer visible. (Hint: If the code above produces an

error, you can also try using the code activesheet.groupboxes.visible=false to

produce the same outcome. For this code to work, the Center Signup Form

worksheet will need to be the active worksheet.)

14. 

With the Center Signup Form worksheet active, turn on Design Mode. Select the

button labeled Click to Clear Information (on the Center Signup Form worksheet)

and view the code associated with this button. Enter VBA code shown in Figure 7

in the Assignment file as the procedure that will occur when CommandButton3 is

clicked. (Hint: Remember there are no spaces before or after the parentheses in

the VBA code, even if it appears that way in Figure 7 in the Assignment file.)

Confirm your VBA code is correct by switching back to the Center Signup Form,

turning off Design Mode, and clicking the button labeled Click to Clear

Information. The range B9:G24 should be blank and none of the controls should

be checked.