CallSheet v.6
CallSheet is an Excel-based utility designed to manage creating and maintaining a Staff-On-Call WorkSheet. This worksheet displays names and phone numbers for emergency callback staff in the Operating Theatre. to be submitted to the After-Hours Coordinator by the Operating Theatre’s Floor coordinator. Thus, the After-Hours Coordinator will have a list of personnel names and phone numbers to call when an after-hours emergency arises.
This worksheet is created weekly by the OT Floor Coordinator, functionally replacing the manually copy-and-paste approach. CallSheet extends Excel through forms and code written in Visual-Basic for Applications, which is part of the Microsoft Excel product. In a nutshell, the way CallSheet works is this: Staff Names and pertinent information such as primary skill and phone numbers is saved in the NameAndPhone worksheet. Names are selected via dropdowns in role sections, and the corresponding staff phone number is selected by the software.
This Help file pertains to CallSheet, version 0.6 (v6).
How to Best Use Help
While CallSheet’s use seems pretty straight-forward to the developer, he realises that his approach to running software could differ wildly to other inhabitants of the solar system run software: hence, this Help file.
Why this approach to a Help file? See the footnote [1].
If the user “runs into a snag”, as in: doesn’t know how to proceed further, she can click on the Help button: there is one on each form and on the main worksheet. Clicking the Help button will bring up this Help document. If the user is on a form, this document will display Help for that form.
It is suggested to make use of the Quick Search feature if a brief perusal of the menu doesn’t reveal what the user needs help with.
For example, let’s say help is needed on the purpose of the “Search by First Name” toggle (tick box) on the AddStaff form:
Moving the mouse cursor over controls on forms will display a brief description of that control in a yellow box called a “tool-tip”. Most form-based controls will display a tool-tip: keep in mind that sometimes the tool-tip text will appear beneath the control instead of next to the cursor.
The tool-tip displays the name of the control first, then a colon, then the description. Make a note of the text in the tool-tip box, click on the Help button, and then, in the Quick “Search docs” field in Help (this document):
…type the name of the button or control and press <Enter>.
If “Search docs” does not produce any results, please notify the developer.
Conventions used in this Document
This Help file uses certain conventions:
Stuff you type on
<Enter> - the ‘<’ and ‘>’ represent a physical key on a keyboard
Things to click on
[ Close ] - represents a clickable button
(_o_) - represents a selected radio button
(___) - represents a deselected radio button
[_X_] - represents a selected tick box
[___] - represents a deselected tick box
Notes
History, Caveats and How to Contact Me
Background
CallSheet was developed after I learned that it was taking personnel almost a half-hour to put a Call Sheet together manually using MS Word. The after-hours coordinator expect a printed document instead of hand-written, for legibility reasons. Searching the list of names and phone numbers and copy-pasting this into another sheet was very time-intensive. Using CallSheet, this process should not take any longer than a minute or so to make the selections and print the CallSheet form.
Caveats
CallSheet is fully contained in a stand-alone Excel workbook. Excel as a database is not ideal, but through the quirky magic of Visual Basic for Applications, it generally works as expected. No question it is definitely an improvement over manual copy-and-paste.
VBA itself is not the stellar language to write solutions in one could hope or wish for, and my knowledge on how it works is a bit less than comprehensive. Therefore, someone following me in trying to maintain/fix the code may wonder at some of the kludges I employed. As I learn more on how to do this all properly, the kludges have been removed. Whilst I tried to be as professional as I could developing this tool, CallSheet is pedestrian code at best.
The developer of this utility makes no claims as to the accuracy or reliability of the data, nor are there any guarantees, implied or real, as to the document’s fitness for purpose. I.e., it is presented and delivered as-is. Please keep this in mind when using this utility. I ask the user’s patience with the code… it is what it is. If you manage to break it, you get to keep both pieces.
While the language it is written in - VBA - is proprietary, the code supporting the use of this document - i.e., the “software” - is FOSS: free/open-source software. Please see the Licence Page for the licence agreement associated with this software.
How to Contact the Developer
Whilst software developers endeavour to write clean, robust software with consistent, predictable behaviour, bugs do happen. The CallSheet developer is committed to supporting CallSheet, hence this document and a web presence on github.
A number of means exist to contact the developer:
To find the most current documentation - a (probably) updated version of this document - go to:
The Worksheets
CallSheet is an Excel workbook comprised of several worksheets.
CallSheet
The first worksheet - called CallSheet - contains the form to be printed. It follows the original paper version in terms of structure, with regions to fill in extra staff and after-hours cases. These features are currently not used, but they are available.
The staff and phone number fields can be filled out manually, but it is faster to use the “On-Call” form, which was the whole point of creating this workbook.
The actual “print area” - what the printer actually prints out - starts on row 110, well down the sheet, out of sight: this is by design, as it is important not to type into this area. The fields have special formulas which link them to corresponding fields at the top of the worksheet, so typing into the print region would remove the formulae and the sheet can not work as designed.
NamesPhones
Normally, this worksheet is only visible when the “On-Call” form is running: otherwise, it is hidden by the software [2]. The columns hold the following data:
Names - sorted, in LastName, FirstName format
Primary Phone - to be rung first
Secondary Phone - to be rung second
Role-ScrubScout - a “1” indicates this staffer performs this role
Role-Anaesthetics - a “1” indicates this staffer performs this role
Role-PACU - a “1” indicates this staffer performs this role
Role-OTA - a “1” indicates this staffer performs this role
Status - a “1” indicates an active status; “0”, inactive
Staff do sometimes return to the department so putting them as inactive preserves their information. If they have retired, of course, it is probably safe to delete their record.
Call History
The software supports maintaining a record on call staff, but as the program is sometimes used to merely enter names manually, the result is that records have not been very accurate.
The Bak Sheets
As a security measure, a copy of the interface (CallSheet) and of the NamesPhones sheet are saved as a backup.
Addendum
Footnotes
System Requirements
“Call Sheet” (known as CallSheet) is an Excel workbook using Visual Basic for Applications (a scripting language) to facilitate creating a on-call document for Operating Theatres and Post-Op Recovery, to submit to the after-hours coordinator. As the software was written in and runs in Excel, there is no installation required except for Excel itself, which the organisation has provided. This workbook requires at least Excel 2007: it will not open in previous versions of Excel. This workbook can be opened anywhere Excel is available. This file best serves the Operating Theatre when stored on a network drive. This document was contained in a csHelp folder, but that proved too difficult to maintain, so the help file is only accessible on the web. Therefore, an internet connection is needed to access help.
Starting CallSheet
When you open CallSheet in Excel 2016, security settings (set by Queensland Health IT services) may issue a security warning:
Some active content has been disabled. Click for more details.
Next to this warning is a button labelled [Enable Content]. Content must be enabled in order for the software to work. When you select [Enable Content], another security warning may appear, asking whether you wish to make this file a ‘Trusted Document’. If you wish to no longer see these warnings, click [Yes]. If you wish to disable warnings for networked files (any files that live on the server instead of on the PC you’re working on), tick the box in the lower left-hand corner.
Note: if all else fails, please consult the worksheet with the red tab labelled “If Nothing Works, Check This!”.
Once content has been enabled, the CallSheet workbook will resize itself to full-screen. The workbook defaults to the “CallSheet” worksheet:
…which has the call sheet form, a worksheet made up of columns and rows, organised by days of the week.
CallSheet’s Start Date
To the right of the names and phone number columns is a date field:
Type the date the CallSheet starts on, which is the Monday of that work-week. Entering the date will autofill the caption that starts with “Week Commencing the” as well as fill in the date and day of the week in the day of the week sections of the worksheet. Note: if the entered date corresponds to a weekday other than Monday, that first day-of-the-week field will turn red:
Selecting Names and Phone Numbers
For each day, the staff column holds the name fields. When a name field is selected, a dropdown arrow appears to the right of the name:
Clicking on this down-arrow will cause a list to appear under the field, containing names of staff who perform the role indicated to the left of the name field:
It is not a comprehensive list of all staff, but only of staff who perform that role. It is alphebatised by first name, as staff are more likely to be known by their first name.
Note: unfortunately, although it is possible to type a name into the field, the user must type the name accurately and exactly as listed or an error message appears:
After a name has been selected, the corresponding phone number (or numbers) will appear in the field or fields to the right of the name.
Date and Time Published
As changes may need to be made - and published - on the CallSheet, it was important to make it clear that a change had been made. Therefore, the date and time the sheet was being published is proclaimed clearly on row 39:
Call Sheet - The Forms
Call Sheet uses names, phone numbers and roles entered into the NamesPhones sheet to populate the Call Sheet fields on the On-Call form. It was developed to facilitate publishing the Call Sheet form for the Operating Theatres and Post-Op Recovery to submit to the after-hours coordinator.
Call Sheet has two forms… the “On-Call” form, launched from the Call Sheet worksheet and the “Add or Edit Staff” form, launched from the “On-Call” form.
The On-Call Form
The main form in Call Sheet is launched via the “Fill Out Call Sheet” button on the “Call Sheet” worksheet:
Whilst the fields on the sheet can be filled out manually, the provided “On-Call” form:
is designed to make the process much faster and less likely to create errors. The “On-Call” form has three basic areas:
The Add or Edit Staff Form
The “Add or Edit Staff” form:
is launched by the green “Add/Edit Staff” button on the “On-Call” form. This form has three basic areas:
The On-Call Form
The main On-Call form drives filling out the CallSheet worksheet quickly and efficiently. The Help button on the form (“OCHelp”) will bring up this page directly.
Name-Order Toggle and Date
In the grey bar at the top of this form are two controls: the name-order toggle and the date field. The name-order toggle (“NameOrder”):
allows the user to search by first (given) names instead of the surname. For those of us who don’t know everyone’s surname, this option was introduced so that the name fields could display names in “GivenName Surname” format if desired, instead of the default “Surname, GivenName” (which is how they are entered and sorted in the NamesPhones worksheet).
Be sure to decide whether you want names displaying first name first or last name first (the default) before you start entering names. This toggle will become disabled when name selection has begun, and the only way to re-enable this toggle is by clearing all the fields.
At the top-right is the date field (“TodayDate”). The current date displays when the form is opened: however, if, say, the user wishes to fill out the call sheets in advance for the weekend, the date can be changed by simply typing in the desired dates or using the date-scroll arrows:
to advance the date.
Names and Phone Numbers
This section has three columns and two main sections: first and second call:
The dropdowns (collectively called “StaffName”) should hold only names of staff that work in that capacity. So, an OTA’s name should never appear in the Scrub dropdown. However, some individuals perform a number of roles, and therefore will appear in more than one dropdown.
The dropdowns can be used a number of ways: they can be opened with the arrow on the right side and the user can scroll down the list until she finds the name she’s after. However, it is infinitely faster to simply type a portion of the name, then press <TAB>.
The dropdown uses a form of predictive text. After choosing the name-order, with each letter the user types, the dropdown tries to autofill the field with a name. When the correct name appears, the user simply presses <TAB> on the keyboard to move to the next dropdown. If the name is not on the “NamesPhones” worksheet or, for some reason, not in the dropdown list, further typing will cease producing autofill text. Keep in mind that unless the “Names-Order” tickbox has not been ticked, the predictive text will assume the default ‘LastName, Firstname’ name order.
As the next dropdown becomes active, the numbers for the previously selected entry populate the text fields (Ring First | Ring Second) next to that dropdown.
This process quickly becomes intuitive, with a little practice. If the user realises the names are not displaying to her liking, she will need to remove all entries in the StaffName dropdowns in order to enable the “NameOrder” tickbox.
Note:
If information (such as phone numbers or role) on a staff member needs editing, it is best to perform that edit prior to assigning call (as in: filling out the On-Call form). Role changes need to be saved to the NamesPhones sheet first so that the dropdowns reflect those changes. For example: if someone who normally only works in PACU is also going to be available for Anaesthetics, that role addition needs to be done in order for that person to show up in the Anaes dropdown.
Action Buttons
The buttons at the bottom of the form consist of five buttons:
The “Write to Sheet” button (“WriteSheet”) writes the selections to the Call Sheet.
The “Clear Sheet” button (“ClearSheet”) clear entries on the sheet and on the form.
The “Add/Edit Staff” button (“AddEditStaff”) brings up the Edit staff form.
The “Help” button (“OCHelp”) brings up this document, opened on the On-Call page.
The “Close” button (“OCClose”) closes the On-Call form.
Add or Edit Staff Information
The “Add or Edit Staff” form is launched from the Staff button in the MSH-CallSheet tab in the Excel ribbon menu:
Find Name in List
The dropdown at the top of the form (“AllStaff”) allows the user to find a staff member’s name and details for editing. The Name-order is alphabetically by given (first) name. The phone number(s) is/are next to the name. Note: there is a number at the end of the field, but it normally won’t be visible and is only useful to the software itself.
A name can be found by typing the name, or by clicking on the down-arrow of the dropdown to display the names in the dropdown list. Select a name by clicking on it, and the details of that individual will display in the fields below the dropdown, including the role that person has in the department.
Staffer Details
Required staff detail information includes:
First Name (“FirstName”)
Last Name (“LastName”)
Primary phone number (“MainPhone”) [3]
Role
The secondary phone number (“SecndPhone”) is not required: however, it is helpful to have in case the individual cannot be reached via the primary number.
Staffer Role
Role selection is done in the dropdown:
Scrub/Scout (“ScrubScout”)
Anaes “thetics” (“Anaes”)
PACU “Post-op recovery” (“PACU”)
Footnotes
Notes
The Code
Included in this Help file are references to the secments of code that makes this product (CallSheet) work. Links are provided in your Help document to bring up this page: they are typically prefaced with “Learn more…”:
In order to make the product truly useful, the developer has decided to make all aspects of how it all works as transparent to the end user as possible. With that end in mind, this page will endeavour to clarify what does what and how on different parts of the document.
One issue that has made developing this product challenging has been the Microsoft “feature” of auto-triggering certain events, such as <Object>_Click methods through changes in the value of other objects. This is therefore the purpose of the form properties: boolean bNoRun and bnRR. Thus, when you select a name in the Combobox StaffNames which runs the bPopFields() method, the bnRR is set to True, preventing the _Click() methods of the Role checkboxes (ChkAN, ChkPA and ChkSS). They check the value of bnRR: if True, they will not run as they would by default.
Editing “The Ribbon”
Between the and the menu tabs at the top of this form is the “MSH-CallSheet” menu tab. The options in the menu [Staff], [About], [Help] and [ ] are all driven by code. It is therefore important to keep in mind that almost nothing will work unless “Enable Content” is clicked (enabled).
The code these button depend on are on the Mdlf modules page.
Staff Names Dropdown
allows the user to search by first (given) names instead of the surname. For those of us who don’t know everyone’s surname, this option was introduced so that the name fields could display names in “GivenName Surname” format if desired, instead of the default “Surname, GivenName” (which is how they are entered and sorted in the NamesPhones worksheet).
Be sure to decide whether you want names displaying first name first or last name first (the default) before you start entering names. This toggle will become disabled when name selection has begun, and the only way to re-enable this toggle is by clearing all the fields.
At the top-right is the date field (“TodayDate”). The current date displays when the form is opened: however, if, say, the user wishes to fill out the call sheets in advance for the weekend, the date can be changed by simply typing in the desired dates or using the date-scroll arrows:
to advance the date.
Phone Number Selection
Selecting a name from a dropdown auto-populates the phone field(s). This process is driven by the following code, first, in the Worksheet_Change() method.
Private Sub Worksheet_Change(ByVal Target As Range)
If bNotInRange Then Exit Sub
Application.EnableEvents = False
Call AssocPhone(ThisWorkbook.Worksheets("CallSheet").Range("I2").value)
Application.EnableEvents = True
End Sub
The first “If” statement calls the bNotInRange() function, which checks that the change was made within the “D” column.
Public Function bNotInRange() As Boolean
Dim sAddy As String, sActCellRow As String, sActCellCol As String
sAddy = ActiveCell.Address
' allow user to either TAB or ENTER out of field
If Not (sAddy = "$I$3" Or sAddy = "$J$2") Then
sActCellRow = Trim(sGetTheRow(sAddy))
sActCellCol = Trim(sGetTheCol(sAddy))
' MsgBox "Row is: " & sActCellRow & "---Column is: " & sActCellCol
If Val(sActCellRow) < 5 And Val(sActCellCol) <> 4 Or _
Val(sActCellRow) > 38 And Val(sActCellCol) <> 4 Then
bNotInRange = True
End If
End If
End Function
The worksheet_Change method then turns off all Excel displaying what it is doing so the user isn’t distracted by a lot of screen flashing and jumping with:
Application.EnableEvents = False
The method then calls the AssocPhone() procedure which finds and puts the phone number or numbers next to the name:
Public Sub AssocPhone(dCurrDate As Date)
Dim sList As String, sEmpName As String, sRecnum As String, sCRow As String, _
sDRow As String, sActCellRow As String, sAddy As String
Dim nSCntr As Integer, nACntr As Integer, nPCntr As Integer, nAllCntr As Integer
Dim rFound As Range
sAddy = ActiveCell.Address
If Len(ActiveCell.value & vbNullString) > 0 Then sEmpName = Trim(ActiveCell.value)
sActCellRow = Trim(sGetTheRow(sAddy))
nAllCntr = Worksheets("CallSheet").Range("A1")
With Worksheets("NameAndPhone")
sList = "B2:B" & Trim(Str(nAllCntr))
On Error Resume Next
Set rFound = .Range(sList).Find(What:=sEmpName, _
LookIn:=xlValues, _
searchOrder:=xlColumns)
On Error GoTo 0
If Not rFound Is Nothing Then
sRecnum = Trim(Str(.Range(rFound.Address).Row))
If Len(.Range("C" & sRecnum).value & vbNullString) > 0 Then
sCRow = Trim(.Range("C" & sRecnum).value)
If Len(.Range("D" & sRecnum).value & vbNullString) > 0 Then _
sDRow = Trim(.Range("D" & sRecnum).value)
Else
sCRow = "No Number Found"
End If
If Not sCRow = "No Number Found" Then
If Len(sDRow & vbNullString) > 0 Then
sCRow = sCRow & " " & sDRow
End If
ThisWorkbook.Worksheets("CallSheet").Range("E" & sActCellRow) = sCRow
End If
End If
End With
Set rFound = Nothing
End Sub
Dealing with Multiple Sheets
Added: 2023-01-10. Currently - probably needs to be set up differently - staff names are sorted alphabetically on separate worksheets (named SS-Scrub/Scout; AN-Anaesthetics;PA-PACU) in the following format: [LastName, GivenName]. Updating last names caused searching for the record to fail, as it was searching for [LastName, GivenName].
The solution was to save to form-level properties (global variables, essentially) record numbers of all the instances - on all relevant sheets - of [LastName, GivenName] in the PopFields() function.
The sheets are second, third, fourth and fifth and are referenced with With Worksheets(nIdx). This function returns sNpR (string: Name phone RecordNumber) which, if the name had been manually typed into the combobox, the PopFields() function is aborted, as rFound will not have found a record for the entered name. In that instance, sSaveRecnos returns a “0”.
Interesting in this the use of With Worksheets(nIdx).
Public Function sSaveRecnos(sLFName As String) As String
Dim nIdx As Integer
Dim rRange As Range, rFound As Range
For nIdx = 2 To 5
' 2-> S02Names, 3-> S03SS, 4-> S04AN, 5-> S05PA
With Worksheets(nIdx)
Set rRange = .Range("A1").CurrentRegion
Set rFound = rRange.Find(What:=sLFName, _
LookIn:=xlValues, _
SearchOrder:=xlColumns)
On Error GoTo 0
If Not rFound Is Nothing Then
' psNpR, psSsR, psAnR, psPaR
Select Case nIdx
Case Is = 2: Me.sNpR = Trim(Str(rFound.Row))
Case Is = 3: Me.sSsR = Trim(Str(rFound.Row))
Case Is = 4: Me.sAnR = Trim(Str(rFound.Row))
Case Is = 5: Me.sPaR = Trim(Str(rFound.Row))
End Select
End If
End With
Next
If Len(psNpR & vbNullString) = 0 Then psNpR = "0"
sSaveRecnos = psNpR
Set rRange = Nothing
Set rFound = Nothing
End Function
This function loops through worksheets. Referencing the worksheets is done by:
making sure the worksheets are contiguous and
each sheet is identified with an index number (which are also contiguous)
Use the “With Worksheets([Index number])” statement, e.g., With Worksheets(2)
Developer’s Notes on v0.6
2023-01-10 TODO: When editing a name, particularly a surname, searching in an array of an existing table is useless. Have added a global var sCurrRecno, but will need to add a recno for entries in the subspecialty sheet(s). These are populated during the PopFields() function.
In the DoSave(), edit the record in each affected table and Sort that table. Also need to update the main sheet at the end.
Older Notes
2019-06-29 Completed: a de-identified copy of this app (artificial data).
Licence Agreement
GNU General Public Licence v3
Terms of General Public Licence, version 3:
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public Licence as published by the Free Software Foundation, either version 3 of the Licence, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public Licence for more details.
You should have received a copy of the GNU General Public Licence along with this programme. If not, see:
Full GNU GPL (need online access to view this document)