EX16_XL_CH12_GRADER_CAP_AS - Teaching Schedule 1.1

!!!!PLEASE INCLUDE SCREENSHOTS OF EXCEL AND STEPS ON HOW TO GET THERE!!!!!

Project Description:

As the department head of the Information Systems Department at a university, you are responsible for developing the class teaching schedules for your faculty. You have a tentative Fall 2015 schedule developed in sequence, but you want to ensure that you are not double-booking classrooms or faculty. To help you review room and faculty schedules, you will need to sort the original list in various ways. In addition, you want to create a model to use as a template for future semesters and to share with other department heads.

Steps to Perform:

Step	Instructions	Points Possible
1	Download and open the file named exploring_e12c2Schedule.xlsx. Save the file as chapter12_LastFirst, replacing LastFirst with your name.	0
2	On the Sequential worksheet, apply the Retrospect theme and the Aspect theme color. Apply Heading 3 cell style to the column labels in the range A4:K4.	10
3	Record a macro named RoomSort. Click and drag to select the range A4:K46 from the Sequential worksheet, then copy and paste the data in cell A4 of the Room worksheet. Do not stop the recording after this step.	10
4	With the macro still recording, click cell A4. On the Data tab, in the Sort & Filter group, click Sort. Using the Sort dialog box, sort the data by Room in alphabetical order. Add a second level sort that sorts the Days column using the following custom sort order: MWF, MW, M, W, TR, T, R, S. Add a third level sort that sorts the Start Time column from earliest to latest. Return to the Sequential worksheet, click cell A1 and stop the recording. Save the workbook as a Macro-Enabled Template.	15
5	With A1 of the Sequential worksheet selected, record a macro named FacultySort. Click and drag to select the range A4:K46 from the Sequential worksheet, then copy and paste the data in cell A4 of the Faculty worksheet. Do not stop the recording after this step.	10
6	With the macro still recording, click cell A4. Create a custom sort that does the following: Sort Instructor in alphabetical order, then by Days with the custom order created in the prior step (Step 4), then by Start Time from earliest to latest. After the sort is complete, return to cell A1 on the Sequential worksheet and stop recording.	10
7	Insert a form control button filling the range E1:E3 in the Sequential worksheet and assign it to the RoomSort macro.	16
8	Edit the text that appears on the button to display the text RoomSort.	2
9	Using the prior steps as a guide, create a second button that displays the text FacultySortfilling the range F1:H3 and assign it to the FacultySort macro. Once completed, save the file as a macro-enabled template. This file format will save your macros.	12
10	Create a new worksheet named Code to the right of the Faculty sheet.	5
11	Open the VBA Editor and display the module containing the room sort. Copy the code beginning with the text Sub RoomSort () and ending with the first instance of the text End Sub. Paste the contents in cell A1 of the Code worksheet. Ensure that the copied code is formatted as Text. Do not close the VBA Editor.	5
12	Display the module containing the faculty sort. Copy the code beginning with the text Sub FacultySort () and ending with the final instance of the text End Sub. Paste the contents in cell F1 of the Code worksheet. Ensure that the copied code is formatted as Text. Close the VBA Editor.	5
13	Save the file as an Excel Workbook (not macro-enabled) with the name chapter12_LastFirst_solution. (This will delete your macros, but the macro instructions are saved on the Code worksheet for grading.) Ensure that the workbooks are displayed in the following order: Sequential, Room, Faculty, and Code. Close the workbook and exit Excel. Submit the chapter 12_LastFirst_solution.xlsx workbook as directed.	0

Total Points

100