for Nahor

E_CH11_EXPV2_EOC_Instructions.docx

Office 2010 – myitlab:grader – Instructions Exploring Series Vol. 2, Chapter 11, EOC

RoomSort Macro

Project Description: In this project you will create a macro that copies and pastes a range of formatted data into a different sheet. The macro will sort the data by three fields, including by a custom list. You will create a command button, attach the macro to it, and then run the macro from the button.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Download, save, and open the Excel workbook named Exploring_e11_Grader_EOC.xlsm. 0 2 Apply the Horizon theme to the workbook. 8 3 In the Sequential sheet, apply the Heading 3 cell style to the range A3:J3. 10 4 Apply the Waveform theme colors to the workbook. 8 5 Begin to record a macro named RoomSort. Accept the default settings in the dialog box. While recording the macro, be sure to follow the steps exactly as specified. Do not switch between windows or worksheets in between steps. 10 6 In recording mode, on the Sequential sheet, select the range A3:J16. Copy the selected range, and then paste it starting in cell A4 of the Room sheet. 10 7 Use the Go To command to go to cell A4 of the Room sheet. 8 8 In the Room sheet, create a multi-level custom sort. First sort by Room in ascending order. Add a sort level to sort by Days, using a custom sort list as follows: MWF, MW, M, W, TR, T, R, S. Then sort by Start Time from earliest to latest. Stop recording the macro. 15 9 In the Sequential worksheet, draw a Form Control button from cells D1:D2. Assign the button to run the RoomSort macro. Set the caption of the button to Run Room Sort, the height to 0.4", and the width to 2.5". 12 10 In Excel, on the Room sheet, delete the data (but not the formatting) in the range A4:J17. Return to the Sequential sheet and deselect Design Mode for the button, if necessary. Click the Run Room Sort button to run the macro and ensure that the macro works as expected. 9 11 In the Microsoft Visual Basic for Applications window, delete any empty comment lines or blank lines in the code, and then copy the entire RoomSort macro from the Sub statement to the End Sub statement. Paste the code into cell A1 of the Code worksheet (the VBA code will appear in cells A1:A28 when pasted). 10 12 Save the macro-enabled workbook as an Excel workbook, using the same file name. Click Yes to save it as a macro-free workbook. Close the workbook. Exit Excel. Submit the Exploring_e11_Grader_EOC.xlsx workbook as directed. 0 Total Points 100

Updated on: 12/28/2010 2 E_CH11_EXPV2_EOC_Instructions.docx