| klste@hotmail.de |
Simple shift planner in Excel VBA.
The shift planner is listed in the "Shift planner" table in area A1:R53.
The user can insert images in the top corners.
All cells with a gray background are filled out by the user.
The names of the areas of application are listed in the gray cells, the reasons for the absence are only given in line 35.
Entries are made in the input mask, first the shift planner is called up, then the month and the calendar week are selected in the shift planner.
A shift schedule is only entered and saved for one calendar week. When you select a calendar week, the saved shift schedule is available on the Shift Schedule sheet.
Then the working shift is selected, then the column and line number, the selected line is selected in the table, the content of the selected line is in the yellow field under the coordinates.
Employees should always be selected, the abbreviations are only selected when used.
Entries are made only in empty selected cells.
Entries are only made in empty cells. The content of the cell can only be deleted, the password for deletion is the number 3.
The program creates a calendar for each year. The calendar shows movable and fixed holidays
The number and names of the fixed public holidays are determined independently by the user. No more than 15 fixed public holidays are assigned.
The program has a list of 16 moveable holidays where the user can only determine whether the moveable holiday is statutory or non-statutory.
All plans are deleted with the "Delete all shift plans" button. The password for deleting is the number 3.
Only 200 employees can be entered. Each registered employee has a corresponding employee account. If an employee is selected in the mask, the "Sheet" button is visible. The button shows the content of the selected account on the "Sheet" sheet.
On the "Lists" sheet, employees are entered in column A, and abbreviations are entered in column D. The number of abbreviations is determined by the user.
1_ Structure of the shift planner. Simple shift planner in Excel VBA.
The simple shift planner offers space for three shifts: early shift, late shift and night shift.
Each shift has four areas:
Injection molding/machining shift manager
Shift manager extrusion
Production
Mill
There are three more areas that apply to each shift:
Vacation
Sick
Miscellaneous
The shift planner with the seven areas is in the "Shift plan" spreadsheet. In the spreadsheet, each user can change the designations of the seven areas as they wish. The spreadsheet is printed out in landscape orientation.
2_ This is how data is entered. Simple shift planner in Excel VBA.
It is entered from an input mask. The input mask is activated with the "Mask" button, this button is located in the "Mask" spreadsheet.
The mask can be moved by clicking at the top with the left mouse button.
In the input mask with the "Shift planner" button, the "Shift planner" frame is called up, from the frame it is entered in the shift plan.
The month is selected, then the calendar week. Entries are always made for a calendar week.
The data entered for each week is always stored in the database. If month and week are selected, the data entered will be displayed in the shift schedule.
Data for early shift is entered in columns A and D.
Data for late shift is entered in columns G and J.
Data for night shift is entered in columns M and P.
If a row is selected in the selected column, the corresponding cell will be activated in the "Shift plan" worksheet, for example A12 or A44. Contents of the selected cell can be seen below the row number in the yellow box.
The name of the employee is entered and, if necessary, the abbreviation is also entered. At the bottom of the shift schedule there is an area for abbreviations and explanations.
In the combo box "Abbreviation" you can select the abbreviation or write any.
In the "Employees" combo box, the name of the employee may only be selected from the list.
Each entry cannot be changed, each entry can only be deleted, so if the selected cell is empty, the "Enter" button will be visible. And if something is entered in the selected cell, the "Delete entry" button can be seen.
When deleting the entry, if the warning is not deactivated, you will be asked for the password, password is the number 3. The warning is deactivated in the input mask by ticking the checkbox "Disable warning about deleting entries".
The lists of abbreviations and employee names are in the "Lists" worksheet. Employee names are in column A and abbreviations in column D.
Font color and background color of employee names are adopted in the shift plan.
You can enter as many abbreviations as there are rows in an Excel spreadsheet.
Only 200 employee names can be entered. Each registered name corresponds to an employee account. When Employee Name is selected in Mask, “Sheet” button is visible. The button creates the selected account in the Sheet worksheet.
3_ Year change. Simple shift planner in Excel VBA.
Before changing the year or changing after the year, you should delete all collected data in the database with the "Delete all shift plans" button. The password for deletion is number 3. The warning is not turned off when this button is pressed.
With the button "Change year" in the program a calendar for selected
Create a year with floating and fixed holidays. 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 moveable 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. And still, in the calendar the date can only be selected from the list, simply entering the date in the combo box does not always work.