Exploring Series Vol. 2 Templates, Styles, and, Macros

Templates, Styles, and, Macros

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_e12_grader_h1.xlsx, and then save the file as e12c1Schedule_LastFirst, replacing LastFirst with your name.

0

2

On the Sequential worksheet, apply Retrospect theme. Apply Heading 3 cell style to the column labels in the range A4:K4, and apply Aspect theme color.

10

3

Record a macro named RoomSort. The macro should copy the range A4:K46 from the Sequential worksheet 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

Click the Faculty worksheet and record a macro named FacultySort. The macro should copy the range A4:K46 from the Sequential worksheet and paste the data in cell A4 of the Faculty worksheet. Do not stop the recording after this step.

10

6

While still recording the macro, 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, 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.

15

8

Edit the text that appears on the button to display the text RoomSort.

5

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.

10

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. The copied code will fill the range A1:A34. 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 A36 of the Code worksheet. The copied code will fill the range A36:A67. Close the VBA Editor.

5

13

Save the file as an Excel Workbook 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

Total Points

100