Appointment calendar with pictures and filters View in combo boxes and in ListBox. Excel VBA

This program is an Excel file programmed in Excel VBA. 

The program allows up to 100 appointments per day. For a better overview, the program can sort the appointments in ascending order of the start time.

 

The program has a personal database for 4000 people, each person can add a picture. 

 

For each calendar day, a list of people whose birthdays fall on that day is created.

 

When searching for appointments or people, the Excel filter function is used in the sheet. If filter view is selected, the filtered data can be seen in the input mask in combo boxes and in the list box.

 

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.

 

When you delete something, the program will first ask you for a password, the delete password is the number 3.

 

The Excel file contains 4 worksheets: "Mask", "Sheet", "Person Database", and "Persons appointments".

 

Everything in the program is done via an input mask. The input mask is activated on the "Mask" worksheet with the "Mask" button.

 

The worksheet “Sheet” contains a list of appointments on the selected day or a list of people whose birthday is on the selected day.

 

A copy of the person database is shown in the “Person Database” worksheet.

 

A list of all appointments for the selected person is shown in the "Persons appointments" worksheet.

 

The input mask contains a calendar at the top left. The calendar consists of combined fields "Month" and "Day" and 7 rows of 8 cells.

The cells in the first row contain the names of the days of the week and the first column contains the numbers of the calendar weeks.

Saturday cells have a green background, Sundays have a blue background, and the selected day has a yellow background.

Public holidays are highlighted in red, non-public holidays in gray. 

If the selected day is a public holiday, the name of this public holiday is displayed in an oblong cell below it, on non-working holidays this cell is red, on non-public holidays it remains gray.

If the input mask is activated, the calendar always shows today's date.

 

The "Current date" button switches the calendar to today's date.

 

A calendar for any year is created in the input mask via the "Change year" button.

In the list of 10 movable holidays, by activating the check box, the selected holiday is marked as a non-working day.

In the list of the 15 fixed holidays, users decide what name and date the holidays have and by ticking the checkboxes you mark the public holidays.

Click on the "Take over" button, enter a 4-digit year in the input field and click on OK. The calendar has been created.

 

All entered appointments are deleted with the “Delete all appointments” button. Password for delete is the number 3.

 

The "Delete personal database" button deletes the personal database. Password for delete is the number 3.

 

Appointments for one day are entered in the "Appointment" frame; no more than 100 entries can be entered for one day.

The following is entered in each appointment: time "from" and time "to", personal number and reason for the appointment.

All appointments entered with the "Sort ascending" button are sorted in ascending order according to the start time "from".

Every appointment entered is given a number, the appointment cannot be changed, it can only be deleted, the password for deleting is the number 3. 

 

The personal data is entered in the "Personal Database" frame, for a maximum of 4000 people. 

Each registered person gets a personal number. Data of each personal number can be modified and each personal number can be deleted, password for delete is number 3.

 

The “Persons Birthdays” frame lists all persons whose birthday coincides with the selected day and calculates the age of each selected person.

 

In the "Persons Appointments" frame, you can see a list of all entered appointments for each selected person.

 

With the "Insert picture" button, a picture is added to each selected person. The button is only visible if a person number is selected.

 

“Remove picture” button will remove image from each selected person, password is number 3. Button is visible only when a person number is selected.

 

Checkbox "Appointment" shows all appointments that were recorded on the selected day.

When this checkbox is checked, the button below the row of checkboxes is titled "Sheet" and is gray. If this button is pressed, the program creates what is in the list box on the "Sheet" worksheet, activates the "Sheet" worksheet and hides the input mask.

In the right header of this sheet is "Appointments from", the date and the number of pages.

 

The "Person Database" checkbox shows the entire person database in the list box.

When the checkbox is checked, the button below the row of checkboxes is titled "Person Database" and is dark orange. If this button is pressed, the program creates a copy of the person database on the “Person database” worksheet, activates the worksheet and hides the input mask.

In the right header of this worksheet is "Person Database" and the number of pages.

The sheet can be filtered: click on cell A1, select "Home", "Sort & Filter", "Filter" from the menu bar.

 

The "Birthdays of persons" checkbox shows everyone in the list box whose birthday coincides with the selected day.

When this checkbox is active, the button below the row of checkboxes is titled "Sheet" and is yellow. If this button is pressed, the program creates what is in the list box on the worksheet "Sheet", activates the sheet "Sheet" and hides the input mask.

In the right header of this worksheet is "Birthdays of", the date and the page number.

 

The "Persons appointments" checkbox shows all appointments of the selected person in a list box.

When this checkbox is checked the button below the row of checkboxes is titled "Persons appointments" and is green. If this button is pressed, the program creates the content of the list box on the "Persons appointments" worksheet, activates the "Persons appointments" worksheet and hides the input mask.

In the right header of this worksheet is "Appointments from", person number and number of pages.

The sheet can be filtered: click cell A3, select "Home", "Sort & Filter", "Filter" from the menu bar. 

If the filtered is to be sorted by day in ascending order: click on cell C3, select "Home", "Sort & Filter", "Sort from A to Z" in the menu bar.

 

The "Filter view database" checkbox shows the filtered person database in the list box.

Only filtered personal numbers are available in the "Person database" frame in the "Persons No." combo box. When the checkbox is checked, the button below the row of checkboxes is not visible.

 

Checkbox "Filter view P. appointm." shows filtered appointments from the selected person in the list box.

The number of the selected person is in the "Persons appointments" frame in the "Person No." combo box and only filtered ID numbers are in the "Appointment ID No." combo box. The other combo boxes "Appointment month" and "Appointment day" are locked. When the checkbox is active, the button below the row of checkboxes is hidden.

 

The list box is white if it contains data from the "Appointment" frame.

The list box is dark orange if it contains data from the "Person Database" frame.

The list box is yellow if it contains dates from the "Persons appointments" frame.

The list box is green if it contains data from the "Persons Appointments" frame.

 

By double-clicking with the left mouse button in the list box, the selected appointment or person number is entered in the corresponding combo box.

 

In the ListBox, the column widths are automatically adjusted to the width of the displayed values.