EX16_XL_CH12_GRADER_CAP_AS - Teaching Schedule 1.1

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.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	Download and open the file named exploring_e12c2Schedule.xlsx, and then save the file as e12c2Schedule_LastFirst, replacing LastFirst with your name.	0.000
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.000
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.000
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.000
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.000
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.000
7	Insert a form control button filling the range E1:E3 in the Sequential worksheet and assign it to the RoomSort macro.	16.000
8	Edit the text that appears on the button to display the text RoomSort.	2.000
9	Using the prior steps as a guide, create a second button that displays the text FacultySort filling the range F1:H3 and assign it to the FacultySort macro. Once completed, save the macro-enabled template.	12.000
10	Create a new worksheet named Code to the right of the Faculty sheet.	5.000
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.000
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.000
13	Save the file as an Excel Workbook (not macro-enabled) with the name e12c1Schedule_solution. Ensure that the workbooks are displayed in the following order: Sequential, Room, Faculty, and Code. Close the workbook and exit Excel. Submit the e12c1Schedule_solution.xlsx workbook as directed.	0.000
	Total Points	100.000