Kapat

Personnel Database That Can Be Added Pictures To Records

excel staff list with image

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 : excel personal template

Report Sheet :excel employee database

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. excel personnel list

For example; To get Staff No from the Data sheet to the Report sheet cell C9, we entered the following formula in cell C9 : excel vlookup =IF(C8="","",VLOOKUP(C8,Data!$B:$N,2,0))
For cell C10 : =IF(C8="","",VLOOKUP(C8,Data!$B:$N,3,0))
Formula of cell C11 : =IF(C8="","",VLOOKUP(C8,Data!$B:$N,4,0))

excel formula calculates how long the staff has worked at the job 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. vba macro codes

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 . excel protect formula

Sub auto_open()
Sheets("Data").Activate
With ActiveSheet
 .Unprotect
 .Cells.Locked = False
 .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
 .Protect AllowDeletingRows:=True
...
End With

excel display image 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     excel image control    the name of picture : Tom Murphy.jpg

VBA codes that allow us to view the picture of personal :excel personal image list

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.

📥 Sample files can be downloaded here