Assignment On Excel Workbook
6 Pages1671 Words678 Views
Added on 2019-09-23
Assignment On Excel Workbook
Added on 2019-09-23
ShareRelated Documents
Shelly Cashman Excel 2016 | Module 11: SAM Project 1aRicky Laine’s MasonryUSER INTERFACES, VBA, AND COLLABORATION FEATURES IN EXCELGETTING STARTEDOpen 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”.oIf 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. oIf 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:oSupport_SC_EX16_11a_AaronE.xlsmoSupport_SC_EX16_11a_Mason.jpgNOTE: 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 clickthe Options button. In the Excel Options dialog box, click the Customize Ribbon option and click the Developer check box. Click the OK button to closethe 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 STEPS1.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 1aSwitch 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 Quotesworksheet 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 22and 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.
Shelly Cashman Excel 2016 | Module 11: SAM Project 1a9.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 aguide. 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 locatedwithin 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 InformationFieldValueFieldValueFirst NameRosieLast NameHunterAddress9 Mill RoadEmailRHunter@example.qm.comCityAlbanyMain Phone Number518-555-1005StateNew YorkAlternative Phone Number518-555-0715Postal Code12201
End of preview
Want to access all the pages? Upload your documents or become a member.