Liting Wang Shelly Cashman Excel 2013 Chapter 10: SAM Project 1a Submission #2 Score is 79 out of 100 1. Go to the Event Schedule - 2018 worksheet and use the password J@Nuary18 to unprotect the worksheet. Unprotect a 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. Use a data form to enter a record. One or more values shown in Figure 4 of the Assignment File were not entered into the Event Schedule - 2018 worksheet (starting in cell A11). 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. Enter a formula using a function. Copy a formula into a range. Change the width of a column. Hide a column. 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. Create and test a macro. Assign a macro to a button. Run a macro. 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. Add an option button to a worksheet. Add controls to a group box. 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. Rename an Option button. 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. Align and distribute option 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. 7/7 0/7 7/7 7/7 7/7 7/7 7/7 0/7 Add controls to a group box. In the Center Signup Form worksheet, the Email, Daytime Phone, Evening Phone, U.S. Mail option buttons should all be contained in the same group box. (Tip: Make sure that each option button's text field - not just text - is completely contained within the group box control.) 9. Format the option button control labeled Email, so that it has a cell link to $K$45. Set the cell link of an Option button. 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 Add a Check Box control to a worksheet. Link a Check Box control to a specific cell. 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. Enter a formula using a function. In the Center Signup Form, cell L44 should contain a formula that uses the INDEX function to determine the value from the named range Request_Type that corresponds with the 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. Use control buttons to run macros. 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.) Change the visibility options of a group box control. 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. Create and Run a VBA macro. 7/7 7/7 0/7 7/7 8/8 8/8 Shelly Cashman Excel 2013 Chapter 10: SAM Project 1a FINN CAREER CENTER Using Macros, Controls and VBA with Excel Author: Liting Wang Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. ad a new copy Event ID JAN_2018_R2 JAN_2018_N1 JAN_2018_C1 JAN_2018_I1 JAN_2018_N2 JAN_2018_C1 JAN_2018_O1 JAN_2018_R2 Event Resume Review Networking Skills: Discovering Your Hidden Network Writing Cover Letters: Rising Above The Pack Interviewing Techniques: Tips And Techniques Networking Skills: Alumni Event Writing Cover Letters: One-On-One Reviews And Critiques The Job Search: Preparing For A Marathon, Not A Sprint Event Type Resume Writing Networking Skills Writing Cover Letters Interview Technique Networking Skills Writing Cover Letters Other Resume Review Event Date Wednesday, January Sunday, January Wednesday, January Wednesday, January Friday, January Sunday, January Sunday, January Tuesday, January 10, 14, 17, 17, 19, 21, 28, 30, 2018 2018 2018 2018 2018 2018 2018 2018 Event Time Event-Location 5:00 PM 3:00 PM 5:00 PM 7:00 PM Lyons-110 5:00 PM Smithfield-100 5:00 PM Warner-280 3:00 PM Warner-280 3:00 PM Warner-200 Building Warner Warner Warner Lyons Smithfield Warner Warner Room 280 380 280 110 100 280 280 200 6 11 7 Click to Enter Contact Information. Click to Submit Information. FINN CAREER CENTER Workshop Mailing List First Name 0 Street 0 Last Name State City 0 Zip Code Daytime Phon 0 Evening Phon Email 0 Request: Select the topics you be interested in learning more about at the Finn Career First Name Last Name Street Submit mation. Click to Clear Information. 0 Contact By: 0 0 0 more about at the Finn Career Center: City State ZIP Code Daytime PhEvening Ph Email Contact By:Request Resume Cover LetteInterviews NetworkingJob Search TOther Email Add to List Contact Request Email Daytime Phone Evening Phone U.S. Mail Add to List Update Information Remove Information PROJECT DESCRIPTION Patrick Fitzgerald is the director of Career Development at Finn Technical Institute, a local community college. The Institute offers workshops on resume writing, interview techniques, and networking skills, along with job placement services for students at their on-campus career center, Finn Career Center. Patrick has asked for your assistance streamlining the process of entering workshop data into the Finn Career Center online listing. He would also like your help streamlining the form used to update the Center's mailing list. GETTING STARTED Download the following file from the SAM website: o SC_Excel2013_C10_P1a_FirstLastName_1.xlsm Open the file you just downloaded and save it with the name: o SC_Excel2013_C10_P1a_FirstLastName_2.xlsm o Hint: If you do not see the .xlsm file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically. With the file SC_Excel2013_C10_P1a_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. If you see a Message Bar with a security warning at top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file (refer to Figure 1 below). Figure 1: Security Warning Message Bar To complete this project you will need to display the Developer tab. To add this tab to the Excel ribbon, click on the File tab to open Backstage view and then click Options. In the Excel Options dialog box, click on the Customize Ribbon option and click the Developer check box (refer to Figure 2 below). Click the OK button to close the Excel Options dialog box and confirm the Developer tab appears in the Excel Ribbon. Figure 2: Customize Ribbon section of Excel Options Dialog Box To complete this project you will need to display the Form button to the Quick Access toolbar. Follow the steps below to add this button to the Quick Access toolbar: o Right click on the Quick Access toolbar and select the Customize Quick Access Toolbar. o Select the Commands not in the Ribbon option from the Choose commands from list. o Find the Form option on the command list (refer to Figure 3 below), select it, and then click the Add button to add the Form command to the Quick Access Toolbar. o Click the OK button to close the Excel Options dialog box and confirm the Form option appears in the Quick Access Toolbar. Figure 3: Customize Quick Access Toolbar section of Excel Options Dialog Box PROJECT STEPS 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) into the Event Schedule - 2018 worksheet. The Event field value is intentionally left blank. Figure 4: New Record for Event Schedule - 2018 Worksheet 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 EventLocation 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. Use Figure 5 as a guide while formatting the Center Signup Form in steps 5-10. Figure 5: Center Signup Form Worksheet 5. Go to the Center Signup Form worksheet. Add an Option Button (Form Control) to the worksheet using Figure 5 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 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 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 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. Use Figure 6 as a guide when adding a record in step 12. Figure 6: Center Signup Form Worksheet with New Record 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 on the following page. Table 1: Center Signup Form Information Field Value First Name Finn Last Name Callahan Street 234 W. Main St. City Norman State Oklahoma ZIP code 73000 Daytime Phone 405-555-2313 Evening Phone 405-555-0003 Email Finn_Callahan@example.fcc.edu 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 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 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 on the following page.) Figure 7: CommandButton3_Click() Procedure 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. Your workbook should look like the Final Figures below and on the following page. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Event Schedule - 2018 Worksheet Final Figure 2: Center Signup Form Worksheet