Course Scheduling Workbook Control

Course Scheduling Workbook Control

Introduction

There are four types of scheduling workbooks:

  • Master template – only one, located in Original Documents library
  • Distribution template – only one, located in Original Documents library
  • Scheduler workbook – one workbook for each Scheduler and term, located in Course Scheduling Workbooks library
  • Composite workbook – only one, located in Course Scheduling Workbooks library

Master Template

Used for:Design of all workbook functions, formatting, help, and instructions

Template from which all other workbooks are generated

Created by:De novo construction

Permissions:Modify: Course Scheduling Owners groupRead:all

Retained:Yes; persistent master template

Worksheets:

Master Template Worksheets
Type / Name / Contents / Source / Cells† / Columns † / Protected † / Hidden †
Info / Info & Tools / Help for Schedulers / original / y / n / n / n
User / Lecture-Lab Schedule / Scheduling of lecture and lab courses by Scheduler / original / y / n / n / n
Additional Instructors / Entry of additional instructors for lecture and lab courses by Scheduler / original / y / n / n / n
Fld Wk-Ind Study Schedule / Scheduling of field work and independent study by Scheduler / original / y / n / n / n
Control / Control Data / Lists, variables, and constants used by workbook / original / n / n / n / n
Lecture-Lab Courses / List of lecture and lab courses offered by school / Values for courses copied from Academics, Courses in AAIS / n / n / n / n
Fld Wk-Ind Study Courses / List of field work and independent study courses offered by school / Values for courses copied from Academics, Courses in AAIS / n / n / n / n

† Highlighted table cells indicate changes from source.

Key:Cells:y = data entry cells (empty, no fill) are not locked

n = all cells are locked

Columns:y = control columns (marked with “HIDE” or “H” in row 1 are hidden

n = no columns are hidden

Protected:y = worksheet is protected with only unlocked cells selectable

n = worksheet is not protected

Hidden:y = worksheet is hidden

n = worksheet is not hidden

Distribution Template

Used for:Template from which scheduler workbooks are generated for a term

Created by:Procedure:

  1. Open master template for read only.
  2. Enable Macros.
  3. Save As to same library (Original Documents) as Excel Macro-Enabled Workbook by clicking on and replacing existing distribution template (which acquires the filename and other properties of the replaced document).
  4. Click Yes until it stops asking
  5. In Control Data worksheet, enter Year, Term, and Start and Stop Dates for term to be scheduled.
  6. On each user worksheet, hide columns so marked and set error checking to OFF.
  7. Protect (select unlocked cells only) and hide each Info and Control worksheet except Control Data.
  8. Save and exit the workbook.
  9. In Properties, revise term and year to match the data entered in step 3, above.

Permissions:Modify:Course Scheduling OwnersRead:SPHIS Readers

Retained:No; overwritten by the next term’s schedule

Worksheets:

Distribution Template Worksheets
Type / Name / Contents / Source / Cells † / Columns † / Protected † / Hidden †
Info / Info & Tools / Help for Schedulers / Master template / n / n / y / n
User / Lecture-Lab Schedule / Scheduling of lecture and lab courses by Scheduler / Master template / y / y / y / n
Additional Instructors / Entry of additional instructors for lecture and lab courses by Scheduler / Master template / y / y / y / n
Fld Wk-Ind Study Schedule / Scheduling of field work and independent study by Scheduler / Master template / y / y / y / n
Control / Control Data / Lists, variables, and constants used by workbook / Master template / n / n / n / n
Lecture-Lab Courses / List of lecture and lab courses offered by school / Master template / n / n / y / y
Fld Wk-Ind Study Courses / List of field work and independent study courses offered by school / Master template / n / n / y / y

† Highlighted table cells indicate changes from source.

Key:Cells:y = data entry cells (empty, no fill) are not locked

n = all cells are locked

Columns:y = control columns (marked with “HIDE” or “H” in row 1 are hidden

n = no columns are hidden

Protected:y = worksheet is protected with only unlocked cells selectable

n = worksheet is not protected

Hidden:y = worksheet is hidden

n = worksheet is not hidden

Scheduler Workbook

Used for:Entry of course scheduling data by scheduler for a term

Created by:Procedure:

  1. Open distribution template for read only.
  2. Enable Macros.
  3. Save As Excel Macro-Enabled Workbook to Course Scheduling Workbooks library with filename = <Scheduler abbrev> <year>-<term #> <term name>, where term # = 0 if Spring, 1 if Summer, and 2 if Fall.
  4. In Control Data worksheet, enter Scheduler.
  5. In each user worksheet, paste Scheduler to each cell in Scheduler column. This must be the abbreviation as literal text. The text mayonly be semi-visible after pasting.
  6. Protect (select unlocked cells, format rows) each user worksheet.
  7. Use AutoFit tool to make sure row formatting is on.
  8. Protect (select unlocked cells only) Info & Tools and Control Data worksheets.
  9. Hide Control Data control worksheet.
  10. Maximize horizontal scroll bar.
  11. Select Info & Tools worksheet.
  12. Save and exit the workbook.
  13. In Properties, enter Title, Year, Term, Scheduler, and Status, where Title = <Scheduler abbrev> Course Scheduling Workbook, <term name> <year> and Status = draft.

Permissions:Modify:Scheduler’s Course SchedulersRead:SPHIS Readers

(until workbook is accepted)

Course Scheduling Owners

Retained:Yes; same library for future reference as needed

Worksheets:

Scheduler Workbook Worksheets
Type / Name / Contents / Source / Cells † / Columns † / Protected † / Hidden †
Info / Info & Tools / Help for Schedulers / Distribution template / n / n / y / n
User / Lecture-Lab Schedule / Scheduling of lecture and lab courses by Scheduler / Distribution template / y / y / y / n
Additional Instructors / Entry of additional instructors for lecture and lab courses by Scheduler / Distribution template / y / y / y / n
Fld Wk-Ind Study Schedule / Scheduling of field work and independent study by Scheduler / Distribution template / y / y / y / n
Control / Control Data / Lists, variables, and constants used by workbook / Distribution template / n / n / y / y
Lecture-Lab Courses / List of lecture and lab courses offered by school / Distribution template / n / n / y / y
Fld Wk-Ind Study Courses / List of field work and independent study courses offered by school / Distribution template / n / n / y / y

† Highlighted table cells indicate changes from source.

Key:Cells:y = data entry cells (empty, no fill) are not locked

n = all cells are locked

Columns:y = control columns (marked with “HIDE” or “H” in row 1 are hidden

n = no columns are hidden

Protected:y = worksheet is protected with only unlocked cells selectable

n = worksheet is not protected

Hidden:y = worksheet is hidden

n = worksheet is not hidden

Version 0, PL Walton, 03/17/11

1