In this template ,we have created a different database where we can save personal information (tel, address, date of birth,email etc.), employment date along with the pictures of the personnel.
The database contains two separate worksheets : Data and Report sheets
Data Sheet :
Report Sheet :
In the VBA listbox control we added to the Report sheet, the names of the personnel are listed. The selected personal’s name from the listbox is displayed in cell C8.
The information of this personal is taken from the Data sheet with formulas based on cell C8 and displayed in the cell range C9: C20.
For example; To get Staff No from the Data sheet to the Report sheet cell C9, we entered the following formula in cell C9 :
For cell C10 :
Formula of cell C11 :
We have created a formula that calculates how long the staff has worked at the job, as months and years. We entered the formula in cell F2 in the Data sheet.
Later, we developed a macro that works after the date of employment is entered in any cell in column E of the Data sheet. This macro copies the formula of cell F2 into the cell in column F next to the cell that where we entered the date in column E. By the formula , the necessary calculation is made and the result is displayed in the cell.
We have added the macro codes to the Worksheet_Change procedure of the Data sheet.
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub On Error Resume Next If Target <> Empty Then Range("F2").Copy Target.Offset(0, 1).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End If End Sub
Formulas that we added to the sheet should not be deleted. To keep the formulas not deleted and the formulas protected ,We added a macro to the VBA Window Module ,that runs when the workbook opened .
Sub auto_open() Sheets("Data").Activate With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True ... End With
We added an Image control to the Report sheet to see the pictures of the records and created the Images folder to the same place as the workbook , put the pictures in this folder.
The picture with the same name as the staff in the folder is displayed in Image control. For example; value of C8 : Tom Murphy the name of picture : Tom Murphy.jpg
VBA codes that allow us to view the picture of personal :
Private Sub Worksheet_Calculate() On Local Error Resume Next Sheets("Report").Image1.PictureSizeMode = fmPictureSizeModeStretch Sheets("Report").Image1.Picture = LoadPicture(ThisWorkbook.Path & "\Images\" & _ Sheets("Report").Range("C8").Value & ".jpg") If Err Then Err.Clear Sheets("Report").Image1.Picture = LoadPicture(ThisWorkbook.Path & "\Images\" & "noimage.jpg") End If End Sub
When double clicked on this displayed picture, the picture is displayed in a larger size. Later when this large picture is clicked returns to its previous size.