Using VBA to cross-reference class & teacher schedules

Hi all -

I'm new to VBA but familiar with programming concepts in general. I'm willing to do the work, but just hoping someone can let me know if what I need to do is possible and point me in the right direction for where I should focus my efforts.

I'd like to create a workbook that lets me work out elementary teacher/class timetables and in the end, allows me to print off teachers' and class' schedules. For example, I want to print off the "grade one" class schedule so they know when they have gym, music, drama, etc, and then also print off the gym teacher's schedule so that he/she knows which classes will be visiting the gym and when. This needs to be replicated for every class (about 35 of them) and every specialty teacher (about 10 of them).

Each day has 8 periods and each cycle has 10 days (meaning, each class & teacher has 80 possible "blocks" to schedule) - I have created a master "template" worksheet with periods down the first column and days 1 through 10 across the header row. In the past, this template would be duplicated and then scheduling would be done by hand - but it is very labour intensive and very prone to human error.

Schedules need to be cross-referenced with each other such that, for example, if the gym teacher teaches the grade one class during period 2 on days 1, 3, 5 and 7, that information will show up on both the class' schedule and the gym teachers' schedule at the same times.

Ideally, I'd love for excel to be able to alert me if I accidentally try to double book a class or a teacher.

So far, I have created a worksheet for each class and each teacher, copied from my "template" worksheet. I also have another worksheet that lists the names of each of the specialty teachers. I then created drop down boxes for each "block" on the schedule so that you can simply click and select the teacher the class will have during each period. I have figured out how to have these drop down boxes trigger a macro, such that when you select a teacher from the drop down, "something" will happen.

What I don't know how to code is that "something".... what I want is if I pick "gym" on Day 1, Period 1, it will first check if the gym teacher is available that period, and if so, will copy the name of that class onto the gym teacher's schedule. Preferably, the opposite could also work - if I pick "grade two" on the gym teacher's schedule during period 4, day 5, that it would check the class to see if it was free that period, and if so, input "gym" into their schedule.

I hope that makes sense.

Am I on the right track?

What I need it to do is:

(User selects a cell (representing a block of time on the schedule), selects a specialty subject or grade from the drop down menu.)
- Copy name of worksheet (=name of the class or subject) [If copying name of the worksheet is not possible, I also have the needed text in a cell elsewhere in the worksheet]
- If identical cell location in destination worksheet (specialty teacher's schedule or class schedule) is empty, paste
- If identical cell location in destination worksheet is full, return error message

Try this code. It belongs in the ThisWorkbook Object.

It will automatically run if you change something in the Range C7:L16, and the color of the tab is that orange or green you used (xlThemeColorAccent3 and 6). That way you change change stuff on other sheets (MASTER, Teachers etc.) or outside the actual schedule slots without worrying about the code running.

I recommend you continue to use a color scheme like that, because Workbook_SheetChange will run on ANY change on ANY sheet in the workbook unless given restrictions, and tab color is a handy restriction to use. Though we could come up with something else if need be.

Please Login or Register  to view this content.