Excel Class Scheduler

If you are a trainer or maintain class enrollment schedules, here is a workbook that might
be useful to you. I spent about three days working on a training class schedule maintainer,
and it is available here to you along with the full VBA source code.

Download sample workbook

For example, if you need to maintain a list of enrollment in training classes, or even for
college professors who need a way to maintain student lists, this workbook is for you.

The workbook has three worksheets :

• A list of currently enrolled students (CurrentEnrollment), along with class name, class date
  and date enrolled
• A list of available classes, with date/time, seat number, instructor (AvailableClasses)
• A proxy sheet which houses the commandbutton used to call the userform, to hide the data
  layer from the end user (Enroll)

Screenshot:

To use it, simply populate the AvailableClasses worksheet with class information. I've
included some sample class names so you can see the required fields and formatting. Be sure
to enter the dates and times as real dates/times, not just random strings formatted as
dates/times. Remove the names from the CurrentEnrollment worksheet before first use
(but don't delete header row). Again I've included some sample names so you
can see the end result.

Hide the CurrentEnrollment and AvailableClasses worksheets (select each one and go to
Format > Sheet > Hide). Then simply publish the workbook and allow others to register themselves
for classes. After selecting a class name and available time, and typing their name in the Name
box, click "Register" and the student name, class name, class date, and date enrolled are all
written to the CurrentEnrollment worksheet, in the next available row.

To unregister, follow the same steps above, but click "Unregister", and the student name will
be removed from the CurrentEnrollment worksheet. You can also do this yourself, on someone
else's behalf.

Optionally, a calendar appointment item can be added to the user's Outlook Calendar. For example if I opened this workbook and registered for a class, I could set up an appointment to remind me that I registered for the class. Just click 'Add to My Calendar' before clicking "Register."

A few other features I should mention:

• The Description box displays the information you type in the "Description" column for each
  class.
• You can view who else is registered for a particular class, after filling out the three
  required pieces of information (Class, Date/Time, Name)
• The "Unregister", "Register" and "See who else is registered" buttons are all disabled
  until the three required pieces of information are provided.
• In order to unregister, name must be typed exactly the same as it was when registering.
• The Name box pre-populates with the Excel username (Application.UserName) and the Windows
  login name (Environ("username")) which can be selected from the combobox, or a new name can
  be typed. There is no validation on the name typed into the box, so for example, there is
  nothing to stop someone from unregistering someone else from a class. One way around this
  is when typing your name into the Name box, add a random string of characters at the end
  of the name, so someone else can't easily guess the name you typed.
• If a class date has passed, or the Current column says "No", the class section will not
  appear on the userform. Users can browse all classes but can only actually register for classes
  that have an available section. So if you want to disable a section (for example, due to not
  enough interest), simply put the word "No" in the current column, and that section won't be
  available for registration. Or just wait until the registration date passes.
• The form checks to see if a class is full, based on the "Seats Available" column. So if you
  enter 5 seats, then only 5 people will be able to register. The sixth person will get an error
  message telling them the class is full. And of course if one of those five unregisters for that
  section, the sixth person may then register.
• No need to delete classes as they expire, you can list all available future and past classes
  and sections, and the userform will automatically manage availability.
• Students can also register for a class even on the day of the class, to allow for last
  minute students who want to attend.
• Calendar appointments must be manually removed, if they were created during the registration.

This code could easily be adapted to write the student list to another workbook, instead
of to another worksheet in the same workbook. It could also be adapted for other purposes,
such as:

• Trip planner
• Library checkout list
• Shopping/to-do list
• Camping trip packing list
• Anywhere where user choices need to be recorded

If you are using it as a Library list, you should use it yourself instead of letting others
use it (or disable the Unregister feature), because the Unregister button allows people to
remove themselves from the list. If they do that, you won't know who has any given book.

Let's review some of the code used in the workbook.

The worksheet button is a CommandButton which comes from the Control Toolbox. I named it
'wksbtnOpenForm' and double-clicked it to add the following code:

Private Sub wksbtnOpenForm_Click()
  Call modMisc.StartTrainingForm
End Sub

'modMisc' is the name of the module used to store the modularized functions, such as the one
to do autofiltering on the CurrentEnrollment worksheet. The sub 'StartTrainingForm' simply
calls the userform which I named 'frmTrainingSignup'.

Sub StartTrainingForm()
  frmTrainingSignup.Show vbModeless
End Sub

It is modeless so we can use it repeatedly without having to keep reopening the form, so we
can allow end users to un/register for more than one class at a time.

There's also another trick I like to use: a Public constant used to define the name of the
"application".

Public Const APPNAME As String = "Training Scheduler v1.0"

I will use the variable 'APPNAME' as the Title argument in our Msgboxes and GetOpenFileName
requests, and the Caption properties for our forms. This will maintain a consistent face to
the end user. It will also make it MUCH easier to change the name of the application, for
example if I upgrade to version 2.0, I only have to change it in this one place, and all the
Msgboxes and Captions will update automatically.

The function that registers the student in the class is another example of having a function
return a Boolean variable to confirm its success/failure to the calling sub.

Function RegisterForClass(strName As String, strClass As String, dtDate As Date) As Boolean

On Error GoTo ErrorHandler

Dim wksEnrollment As Excel.Worksheet
Dim rng As Excel.Range
Dim NextRow As Long
Dim arrData() As Variant

Set wksEnrollment = Worksheets("CurrentEnrollment")
Set rng = wksEnrollment.Cells(1, 1)

NextRow = WorksheetFunction.CountA(wksEnrollment.Range("A:A"))

arrData = Array(strName, strClass, dtDate, Format(Date, "mm/dd/yyyy"))

rng.Offset(NextRow, 0).Resize(1, 4).Value = arrData

RegisterForClass = True
GoTo ExitProc

ErrorHandler:
RegisterForClass = False

ExitProc:
Set rng = Nothing
Set wksEnrollment = Nothing
Erase arrData
End Function

This function takes the student name, class name and class date and writes them to the next
available row of the worksheet. It is called from the "Register" button, after making all the
necessary checks. Notice how we also use the array-writing technique which I demonstrated
in the previous few posts. The Array function assigns four values to the arrData array,
which is then dumped to the worksheet in a resized range.

Let me show you what I did to enable/disable the Register buttons. First I had to figure out
which information was required. That was easy: Class Name, Date/Time, Name. This function,
like the RegisterForClass function above, returns True/False to the calling sub.

It returns True if all of the required information is provided, by checking if something is
selected in the Class Name listbox, the Available Dates listbox, and something is entered
into the Name combobox. If any of them are False, the whole thing is False. Only if all
conditions are True can the whole thing be True.

For reference when viewing the code below:

• lstClassName – listbox with class names
• lstAvailableDates – listbox with dates and times
• cboName – combobox with list of student names

Private Function ReadyToRegister() As Boolean
' returns True if a class is selected, a date is selected, and something
' (ideally a name) is typed into the Name box

Dim i As Long
Dim bIsAClassSelected As Boolean
Dim bIsADateSelected As Boolean
Dim bIsANameEntered As Boolean

' check if a class and date/time are selected, and the name box is filled in
With lstClassName
  For i = 0 To .ListCount - 1
    If .Selected(i) Then
      bIsAClassSelected = True
      Exit For
    End If
  Next i
End With

With lstAvailableDates
  For i = 0 To .ListCount - 1
    If .Selected(i) Then
      bIsADateSelected = True
      Exit For
    End If
  Next i
End With

If cboName.Value <> "" Then
  bIsANameEntered = True
End If

If (Not bIsAClassSelected) Or (Not bIsADateSelected) Or (Not bIsANameEntered) Then
  ReadyToRegister = False
ElseIf bIsAClassSelected And bIsADateSelected And bIsANameEntered Then
  ReadyToRegister = True
End If

End Function

Now, we have no way to tell, when someone is typing their name, if they have already selected
a class, and if someone is selecting a date, if their name is typed on. Rather than writing
some code in the AfterUpdate Event of each form control, to check every other form control,
simply call the ReadyToRegister sub per below.

Here, the control we are working with is the Name combobox, which I named 'cboName'. The code
makes one call to the ReadyToRegister function, and assigns it to a Boolean variable. Then
the Enabled Property of each of the Commandbuttons on the form can be toggled depending on
the return value. If it returns True, then all of the information is filled in, and all of
the buttons may be enabled. If it returns False, then one or more pieces of information are
missing, and the buttons should remain disabled.

Private Sub cboName_Change()
Dim bRTR As Boolean

bRTR = ReadyToRegister

  btnRegister.Enabled = bRTR
  btnUnregister.Enabled = bRTR
  btnWhoElse.Enabled = bRTR
End Sub

FYI, I had originally written the code like this:

Private Sub cboName_Change()

  btnRegister.Enabled = ReadyToRegister
  btnUnregister.Enabled = ReadyToRegister
  btnWhoElse.Enabled = ReadyToRegister
End Sub

But this means three separate calls to the same function, which is unnecessary, since it
is returning a Boolean value which we can capture once and reuse.

The rest of the code mostly uses Autofilter to accomplish most of its functions. Check it out and let me know what you think!

Download sample workbook

Enjoy,
JP