| klste@hotmail.de |
Flexible shift planner and duty planner in Excel VBA
This program is not tied to a specific shift schedule such as a 4-shift system or a 3-shift system; it creates shift schedules or duty rosters of any kind, making the program a flexible planner.
The Excel file has 3 sheets: "Mask", "Sheet" and "Legend".
The "Sheet" worksheet can contain data from 4 reports: "Calendar", "Department_Selected employee", "Place of work" and "Selected Department".
The "Legend" spreadsheet contains the names of activities, their abbreviations and codes. Each abbreviation/code can be formatted in any way, the abbreviation/code will be transmitted afterwards only with the formatting. The spreadsheet also contains the names of jobs, names of departments and names of employees.
In this program, everything is entered via an input mask. The input mask is activated on the "Mask" worksheet using the "Mask" button.
It is only entered if 5 coordinates are selected; Month, date or week, department, employees and place of work.
The selected accounts can be seen in the list box.
Views are selected using three checkboxes: "Department_Selected employee", "Place of work", "Selected Department", with the list box having a different color for each checkbox.
With button "Sheet" each selected account will be shown in the spreadsheet.
When year is over, database will be deleted with button "Delete database". Password for deletion is the number 3.
The "Change year" button creates a calendar for any year.
Sheet "Legend". Flexible shift planner and duty planner in Excel VBA
In the "Legend" sheet, in column "A" there are names of activities and in column "B" there are abbreviations and codes of the activities. Number of activities is not limited. Each abbreviation/code can be formatted in any way, the abbreviation/code will be transmitted afterwards only with the formatting. The designations of activities with abbreviation/code should contain a lot of information so that you can immediately see where and what the selected employee has done or will do later when evaluating.
Column "C" contains places of works. The program only processes the places of works that are in area C2:C101.
In the area from D1 to W1 there are designations of 20 departments. Program works only with the 20 departments.
Each department can have up to 100 employees, all of whom are in the area D2:W101. Program edits employees only from the area.
This is how it is entered into the database. Flexible shift planner and duty planner in Excel VBA
In this program, everything is entered via an input mask. The input mask is activated on the "Mask" worksheet using the "Mask" button.
It is only entered if 5 coordinates are selected; Month, date or week, department, employee and workplace. When all this is selected, a blue button appears, entries are made for the whole week or just for one day, and the selected time period is on the button.
If the entry is made for the whole week, then the entry is made from Monday to Friday, for Saturday and Sunday the entry is not made weekly, for these days you should enter one day each.
If activity is selected Abbreviation/code of the activity will be entered, if activity is not selected then entered will be deleted.
Entries are made in three accounts: "Department_Selected employee", "Place of work" and "Selected department".
The abbreviation/code for the selected day or week is entered in the "Department_Selected Employee" account. If something has already been entered for the selected period, you can change it by entering a different Abbreviation/code, or delete it by entering an empty string in the Abbreviation/code.
In the "Place of work" account, the employee name is first entered for the selected day or week, and then the abbreviation/code for the employee name only. Only 20 employees can be entered for a “workplace” in a month. If something has already been entered for the selected employee in the selected period, you can change it by entering a different abbreviation/code, or delete it by entering an empty string in the abbreviation/code. If abbreviation/code for selected employee is deleted in whole month, employee name will be deleted in month.
The "Selected department" account contains all employee names that are entered in the "Legend" spreadsheet for the department. The abbreviation/code for the selected day or week is only entered for the selected employee. If something has already been entered for the selected period, you can change it by entering a different abbreviation/code, or delete it by entering an empty string in the abbreviation/code.
Evaluation of the entered data. Flexible shift planner and duty planner in Excel VBA
The selected accounts can be seen in the list box.
Views are selected using three check boxes, with the list box having a different color for each check box. In the list box, if the color of the list box is white or yellow, the width of the columns is adjusted according to the length of the entered data.
By double-clicking with the left mouse button in the list box, the date is selected, which is entered in the "Date" combo box.
The "Department_Selected employee" checkbox indicates the employee's account in the whitelist.
The "Place of work" checkbox shows the work account in the yellow list.
The "Selected Department" checkbox displays all employee accounts of the selected department in a green list.
The list always only shows data for the selected month.
Below the three checkboxes is the Sheet button. It always has the same color as the selected checkbox. With the "Sheet" button, the annual schedule of the selected account is created in the "Sheet" worksheet.
In the Year Schedule for "Department_Selected employee", columns A through D are the calendar and column E at the beginning of the month is the employee's name. Below are the abbreviations/codes entered for this account. In months in which nothing was entered, there is no employee name at the beginning of the month. The right header contains the employee's name and the number of pages. Abbreviations/codes indicate where and what the employee of record has done or will do.
In the yearly schedule for "Place of work", columns A to D are the calendar and from column E to column X are the entered employees at the beginning of the month (no more than 20 per month), since underneath are the abbreviations/codes entered for each employee. The right header contains the designation of the workplace and the number of pages. Abbreviations/codes indicate where and what the employees on record have been or will be doing.
In the yearly schedule for "Selected Department", columns A to D are the calendar and from column E to column CZ at the beginning of each month are to employees belonging to the department, below that are the entered abbreviations/codes for each employee. The right header contains the name of the department. Abbreviations/codes indicate where and what employees have done or will do.
Change year. Flexible shift planner and duty planner in Excel VBA
Before changing the year or after changing the year, you should use the "Delete database" button to delete all the data collected in the database. Password for delete is the number 3.
The "Change year" button will create a calendar for the selected year with floating and fixed holidays in the program. The user determines the number and names of the fixed holidays. A maximum of 15 fixed public holidays can be assigned. The program has a list of 16 moving holidays. Tick the checkbox of public holidays.
Click on the "Take over" button, enter a four-digit year in the input field and click on "OK". The calendar is created.
Public holidays have a red background color in the calendar.
Special feature of the program. Flexible shift planner and duty planner in Excel VBA
In this program, the "Legend" sheet is a database, data in this database is entered or changed directly in this sheet. The data from this spreadsheet is actively used in the input mask.
When creating a year-end, the input mask is hidden and the year-end of the selected account is created on the "Sheet" worksheet.
If something changes in the "Legend" spreadsheet during this time, this change is adopted by the program. This program is programmed in such a way that these changes will not cause the Excel file to crash.
Regional format. Flexible shift planner and duty planner in Excel VBA
If the regional format is changed, a message appears when activating the input mask: "The language settings have changed, the names of the months and days of the week are now different from what was probably entered. In order for the program to work without failures, the year must be changed again”.
After the turn of the year, the calendar adapts to the format of the region and the message no longer appears.