| klste@hotmail.de |
Personnel deployment planning, plant staffing, shift planning. Excel VBA
With this program, employees are allocated to existing jobs, Lines, plants and various existing jobs within the department, taking into account their qualifications, education and work experience.
The distribution or scheduling works like this:
Select date, department and workplace (Line, system, laboratory, cost centres), time from and time to, then the employees are selected.
The required employees are selected as follows:
a copy of the personnel database is made, the copy is filtered according to the required qualifications and then it is filtered whether the employee is currently able to work and not registered elsewhere.
No more than 6 people are entered in one entry. If the selected object needs more staff, you can add more people to the workplace in the following records.
The personnel database is designed for only 500 personnel numbers.
Personnel’s planning is designed for only 10 departments.
Each department may only have 30 application areas (Lines, systems, cost centers...)
The first name of each person can be formatted as desired, name formatting is planned.
1_Sheet "Database". Personnel deployment planning, plant staffing, shift planning. Excel VBA
In the "Database" sheet, each user can enter data on personnel, departments and workplaces.
In the "Database" sheet, columns A to G contain personnel data. A maximum of 500 people can be entered.
Only first and last name are displayed in the plan, the formatting of the cells in which the names are located, such as font color and background color, is also adopted.
Sickness and holiday cases of employees are entered into the database as current information.
In column H, the user can enter up to 10 departments.
In 10 columns, from J to S, the user can enter up to 30 workplaces for each department.
Everything else in this program is entered from an input mask that is activated on the "Mask" worksheet with the "Mask" button.
The distribution or scheduling works like this:
Select date, department and workplace (Line, system, laboratory, cost centres), time from and time to, then the employees are selected.
The required employees are selected as follows:
a copy of the personnel database is made, the copy is filtered according to the required qualifications and then it is filtered whether the employee is currently able to work and not registered elsewhere.
Here is an example of how this program works.
The personnel requirement for one day is planned in department Dept1 for Line 11.
This Line is operated by a forklift driver, a forklift driver is used for the early shift.
In the Personnel Database frame, select the Personnel Database check box and then click the Personnel Database button.
The input mask is hidden and a copy of the personnel database is displayed in the "Personnel database" sheet. The filter is used in the copy: click cell A1, select "Start", "Sort and Filter", "Filter" from the menu bar. Now filter by qualification and then by availability.
Activate the "Show filter" checkbox in the "Personnel database" frame; only filtered values appear in the list field. Select an available forklift driver from the list by double-clicking with the left mouse button or simply select a personnel number or a name. A yellow label is visible below, if the selected employee is already entered, this is written in the yellow label.
If the selected employee has not yet been entered, select personnel number 1 in the "Show number in" combo box that appears and then click on the "Show" button. The employee can be included in the planning.
For the Line11 in the early shift you need 1 Machine drivers, 2 Machine operators and 4 Production helpers. They are entered in the same way as for the forklift driver, just note that the entry is only entered if all employees are selected and the "Entry number" field should be empty! In the database in the additional information you can see for which systems the Machine drivers, and Machine operators are trained.
Late shift and night shift require the same number of employees as the early shift.
3_Checkboxes and buttons. Personnel deployment planning, plant staffing, shift planning. Excel VBA
If the "Department" checkbox is checked, the "Sheet" button on the left turns white. When you click it, the mask is hidden and the "Sheet" worksheet is activated. The worksheet contains the personnel distributions created for the entire department for the selected day. The right header of this sheet contains the department name, date and page number.
If the "Workplace" checkbox is clicked, the "Sheet" button on the left turns blue. When you click it, the mask is hidden and the "Sheet" worksheet is activated. The worksheet only contains work schedules created for the work center for the selected day. The right header of this sheet contains the name of the department, designation of the workplace, the date and the page number.
If a personnel number is selected and the "Personnel number" check box is activated, the "Sheet" button on the left turns yellow, when you click it the mask is hidden and the "Sheet" worksheet is activated. The worksheet contains only the personnel distribution plan created for the selected personnel number on the selected day. The right header of this sheet contains the personnel number, date and page number.
If you click on the "Clear all fields" button, 6 fields for personnel numbers and the field for the entry number will be cleared.
When you select an entry number, the Enter button disappears and the Delete Entry button appears.
The Delete Entry button only deletes the selected entry. The password for deleting is the number 3.
4_ListBox. Personnel deployment planning, plant staffing, shift planning. Excel VBA
By double-clicking with the left mouse button in the list box, the selected entry number or personal number is entered in the corresponding combo box.
In the ListBox, the column widths are automatically adjusted to the width of the displayed values.
5_Calendar. Personnel deployment planning, plant staffing, shift planning. Excel VBA
The "Current Date" button switches the calendar to today's date.
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 10 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" button. The password for deleting is the number 3.