Watch Video – Create a Database in Excel with Pictures
Step 1: Create a Header Row in a Worksheet
- Open a new Excel workbook.
- Create a new worksheet (you can name it Employee Database or something similar).
- In the first row (Row 1), create column headers for each section of information you want to store. For example:
- Emp No
- Name
- Address
- Phone
- Designation
- DOB (Date of Birth)
- Photo (for storing employee pictures)
You can manually enter data for each employee directly into this worksheet, or we can use a UserForm (created using VBA) for data entry.
Step 2: Design a UserForm for Entering Data
- Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
- Click on Insert > UserForm to create a new UserForm.
- Design your UserForm with the necessary fields:
-
- Add six TextBox controls (one for each piece of information: Emp No, Name, Address, Phone, Designation, and DOB).
- Add corresponding Label controls next to each TextBox.
- Include an Image control to display employee photos.
- Add four CommandButton controls (e.g., “Add Employee,” “Clear,” “Save,” and “Exit”).
- Customize the appearance (background color, font, etc.) as desired.
Remember to name each component (TextBox, Label, Image, and CommandButton) appropriately, as these names will be used in your VBA code.
Step 3: Writing VBA Code
- We need to create some code that will open a window when we click the Add Image button. This window will prompt us to select a picture of an employee. To achieve this, follow these steps:
- Double-click on the Add Image button.
- The code window will open.
- Write the following code in that window:
Private Sub cmdAddUpdate_Click()
imagePath = Application.GetOpenFilename("Image Files (*.bmp;*.jpg;*.gif;*.png),*.bmp;*.jpg;*.gif;*.png", , "Select the Image of employee")
If imagePath = False Then Exit Sub 'user clicked cancel
img_Emp.Picture = LoadPicture(imagePath)
End SubHow Does the Code Work?
-
- The line
imagePath = Application.GetOpenFilename("Image Files (*.bmp;*.jpg;*.gif;*.png),*.bmp;*.jpg;*.gif;*.png", , "Select the Image of employee")opens a file dialog box. This dialog box will display only image files of types.bmp,.jpg,.gif, and.png. The file path of the selected image will be stored in a variable namedimagePath. - The condition
If imagePath = False Then Exit Subchecks if no image is selected. If that’s the case, the subroutine will exit. - Otherwise, if a valid image file is selected, it will be loaded into the
img_Empcomponent of the UserForm. - If no image is selected, then the sub will exit.
- The line
img_Emp.Picture = LoadPicture(imagePath)
- Let’s assign code to the Delete button (
cmdDel). When we click on this button, it will remove the loaded image from the UserForm. To do this:- Double-click on the Delete button.
- Paste the following code:
Private Sub cmdDel_Click()
img_Emp.Picture = Nothing
End Sub- Finally, we’ll assign code to the Save button (
cmdSave_Click). When we click on the Save button, all the entered data can be transferred into the Database worksheet. Before that, we’ll also need to write some other subroutines to support thecmdSave_Clickfunctionality.
VBA Code Syntax:
Option Explicit:
Dim database As Worksheet
Dim imagePath As Variant
Dim db_range As String
Dim r As Long 'representing first empty row from the top
Public Function lastRow(ws As Worksheet) As Long
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
End Function
Private Sub UserForm_Activate()
Set database = Worksheets("Sheet1")
r = lastRow(database) + 1
db_range = "A1:G" & r
End Sub
Private Sub cmdSave_Click()
On Error Resume Next
r = lastRow(database) + 1
database.Range("A1").Cells(r, 1) = Database_Entry_Form.txtEmpNo.Value
database.Range("A1").Cells(r, 2) = Database_Entry_Form.txtEmpName.Value
database.Range("A1").Cells(r, 3) = Database_Entry_Form.txt_Add.Value
database.Range("A1").Cells(r, 4) = Database_Entry_Form.txt_Tel.Value
database.Range("A1").Cells(r, 5) = Database_Entry_Form.txt_Designation.Value
database.Range("A1").Cells(r, 6) = Database_Entry_Form.txt_DOB.Value
If (IsNull(img_Emp.Picture)) Then
'do nothing
Else
Dim selectedCell As Range
Dim imgHeight As Long
Dim imgWidth As Long
Dim imgRatio As Double
Dim img As Shape
'get selected cell
Set selectedCell = database.Range("A1").Cells(r, 7)
'get image height and width
imgHeight = img_Emp.Picture.Height
imgWidth = img_Emp.Picture.Width
'resize image height to 40 while maintaining aspect ratio
imgRatio = imgHeight / imgWidth
imgHeight = 40
imgWidth = imgHeight / imgRatio
'set row height of selected cell to match image height
selectedCell.EntireRow.RowHeight = imgHeight + 5
selectedCell.HorizontalAlignment = xlCenter
selectedCell.VerticalAlignment = xlCenter
'insert image in selected cell
Set img = ActiveSheet.Shapes.AddPicture(Filename:=imagePath, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=selectedCell.Left + (selectedCell.Width - imgWidth) / 2, _
Top:=selectedCell.Top + (selectedCell.Height - imgHeight) / 2, _
Width:=imgWidth, _
Height:=imgHeight)
img.Name = "Pic" & Database_Entry_Form.txtEmpNo.Value
End If
Call ExtendNamedRange
End SubHow Does the Code Work?
- Variable Declarations:
- Four variables are declared at the beginning of the code. These variables will be used in various procedures later on.
database: Represents a worksheet (presumably named “Sheet1”).imagePath: Stores the path of an image file.db_range: A string that defines a range in the database worksheet.r: A long integer used to keep track of row numbers.
- Four variables are declared at the beginning of the code. These variables will be used in various procedures later on.
- Custom Function –
lastRow:- The
lastRowfunction is defined. It takes a worksheet (ws) as an argument. - It determines the last used row in column 1 (column A) of the specified worksheet using the
End(xlUp).Rowmethod. - The function returns the row number of the last used row.
- The
- UserForm Activation –
UserForm_Activate:- When the UserForm is activated (presumably a form in Excel), the following actions occur:
- The
databasevariable is set to reference the “Sheet1” worksheet. - The
rvariable is calculated by adding 1 to the last used row number in thedatabaseworksheet. This ensures that we find the first empty row for data entry. - The
db_rangestring is constructed to define a range from cell A1 to column G and rowr.
- The
- When the UserForm is activated (presumably a form in Excel), the following actions occur:
- Save Button Click –
cmdSave_Click:- This code runs only when the “Save” button (
cmdSave) is clicked. On Error Resume Nextallows the code to continue even if an error occurs.- The last used row of the
databaseis determined using the customlastRowfunction. - 1 is added to the last row number to find the first empty row (
r). - Text values from different TextBoxes in the UserForm (e.g.,
Database_Entry_Form.txtEmpNo.Value) are stored in their corresponding cells in thedatabaseworksheet. - If the image (
img_Emp.Picture) is null (not selected), nothing happens.
- This code runs only when the “Save” button (
- Checking for Image Presence:
- First, the code checks whether the image component
img_Empcontains an image or not. If it doesn’t contain any image, no further action is taken.
- First, the code checks whether the image component
- Variable Definitions:
- If the image component
img_Empcontains an image, the subsequent code will run. - Several variables are defined for use in the following steps:
selectedCell: Represents the cell in the database worksheet where the image will be stored.imgHeight: Stores the height of the loaded image.imgWidth: Stores the width of the loaded image.imgRatio: Represents the aspect ratio of the image.img: Refers to a shape (presumably an image) that will be added to the worksheet.
- If the image component
- Setting the Selected Cell:
- The
selectedCellvariable is assigned to the cell in the database worksheet where the image will be stored. This cell corresponds to rowrand column 7 (column G).
- The
- Calculating Image Dimensions:
- The height and width of the loaded image (
img_Emp.Picture) are determined. - The aspect ratio of the image is calculated.
- The desired height (
imgHeight) is set to 40, and the corresponding width (imgWidth) is adjusted while maintaining the same aspect ratio.
- The height and width of the loaded image (
- Adjusting Row Height:
- The row height of the cell where the image will be stored is increased by 5 units. This ensures that the image fits comfortably within the cell.
- Adding the Image to the Worksheet:
- The
imgshape is created using theAddPicturemethod. - The image file specified by
imagePathis loaded into the shape. - The position and dimensions of the shape are set based on the
selectedCellposition and the calculatedimgWidthandimgHeight.
- The
- Assigning a Name:
- The name of the shape is set to “Pic” followed by the value from the
txtEmpNoTextBox in theDatabase_Entry_Form.
- The name of the shape is set to “Pic” followed by the value from the
- Updating Image Name Based on Employee ID:
- The image name that has been inserted is modified to be related to the employee ID. This ensures that we can easily retrieve the image when needed by referencing the employee ID number.
Extending the Named Range for Employee Data:
- When searching for employee data in the database using the Employee ID, we need to specify the range within which we want to search. To achieve this:
- We have already named the used range for data storage.
- Additionally, after entering a new entry, we need to extend the named range to include the new data.
- For this purpose, a separate subroutine called
ExtendNamedRangehas been written. We will explain the code for this subroutine later when we discuss how to search for an employee’s information using their Employee ID.
Close Button Functionality:
- We also need to write code so that when we click the “Close” button, it will close down the UserForm.
Ready for Data Entry:
- With these adjustments, our code is now ready for data entry.
Private Sub cmdClose_Click()
Unload Me
End SubNow, our code is ready for data entry.
Read More: How to Create a Simple Database in Excel VBA
Step 4: Running UserForm to Enter Data into Database
Let’s walk through the steps to use the Database_Entry_Form UserForm for entering data into the database worksheet. Follow the instructions below:
- Run the UserForm:
- Click on the
Database_Entry_FormUserForm. - Click the Run button at the top (presumably in the Excel interface).
- Click on the
- Entering Data:
- The UserForm will open up.
- Enter all the necessary data in the UserForm. This may include details like employee name, address, telephone number, designation, and date of birth.
- To add the image of the employee:
- Click on the Add Image button.
-
-
- A window will open, prompting you to select the image file from your computer using the File Explorer.
- Choose any image and click Open.
-
-
-
- The selected image will load into the UserForm.
-
- Saving Employee Information:
- To save the employee’s information:
- Click on the Save button on the UserForm.
- The inserted information, along with the image, will be stored in the database worksheet.
- Data Storage:
- The information will be safely stored inside the database worksheet.
-
- You can continue entering data for as many employees as needed. In your example, you’ve entered data for a total of 5 employees.
- Closing the UserForm:
- When you’re done entering data, click the “Close” button to close the UserForm.
- Finally, the database looks like this:
- Database Ready:
- Your database is now ready, complete with pictures.
- Keep in mind that while Excel provides basic database functionality, it doesn’t offer all the features of dedicated database software (such as advanced data analysis, reporting, and queries).
- To enhance the functionality further, you mentioned creating a search function in another sheet. This will allow you to search for employee details using their Employee ID.
Step 5: Creating Database Search System
In this step, we’ll create a search system on another sheet that looks like the following:
- Enter the Employee Number (Emp No) in cell F5.
- Automatically retrieve the employee’s other information on the left side.
- To achieve this, follow these steps:
- Open a new module in your Excel workbook.
- Paste the following VBA code:
VBA Code:
Sub ExtendNamedRange()
Dim lastRow As Long
Dim ws As Worksheet
Dim namedRange As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set namedRange = ws.Range("A1").CurrentRegion
lastRow = namedRange.Rows.Count
With ws
.Names.Add Name:="db_Sheet", RefersTo:=.Range("A1:G" & lastRow)
End With
End Sub
Sub DeleteAllPictures()
Dim pic As Shape
For Each pic In ActiveSheet.Shapes
If pic.Type = msoPicture Then
pic.Delete
End If
Next pic
End Sub
Sub Get_Details()
Dim look_up_Value As Variant
Dim look_up_Range As Range
Dim result As Variant
' Set the lookup value
look_up_Value = Worksheets("Sheet2").Range("F5").Value
' Set the lookup range
Set look_up_Range = Worksheets("Sheet1").Range("db_Sheet")
' Perform the VLOOKUP and store the result
For i = 1 To 5
Worksheets("Sheet2").Range("C5").Cells(i, 1).Value _
= Application.WorksheetFunction.VLookup(look_up_Value, look_up_Range, i + 1, False)
Next i
End Sub
Sub Employee_Pic()
On Error GoTo ErrHandl
'Deleting Existing Pictures
Call DeleteAllPictures
Dim picName As String
Dim picHeight As Double
Dim picWidth As Double
picName = "Pic" & Range("F5").Value
'Copy the picture from Sheet1 and paste it into Sheet2
Sheets("Sheet1").Shapes(picName).Copy
Sheets("Sheet2").Range("C4").PasteSpecial
'Name the pasted picture in Sheet2 with the original name from Sheet1
Sheets("Sheet2").Shapes(Sheets("Sheet2").Shapes.Count).Name = picName
'Set the height and width of the cell to match the pasted picture's dimensions
'determining actual pic width and height
picHeight = Sheets("Sheet2").Shapes(picName).Height
picWidth = Sheets("Sheet2").Shapes(picName).Width
'determining aspect ration
aspect_Ratio = picWidth / picHeight
picHeight = Sheets("Sheet2").Range("C4").RowHeight - 5
picWidth = aspect_Ratio * picHeight
'Setting new height and width
Sheets("Sheet2").Shapes(picName).Height = picHeight
Sheets("Sheet2").Shapes(picName).Width = picWidth
'Center and middle align the pasted picture inside the cell
Cell_Height = Sheets("Sheet2").Range("C4").Height
Cell_Width = Sheets("Sheet2").Range("C4").Width
With Sheets("Sheet2").Shapes(picName)
.Top = Sheets("Sheet2").Range("C4").Top + (Cell_Height / 2) - (.Height / 2)
.Left = Sheets("Sheet2").Range("C4").Left + (Cell_Width / 2) - (.Width / 2)
End With
Call Get_Details
Exit Sub
ErrHandl:
MsgBox "No Data Found of this employee"
For i = 1 To 5
Worksheets("Sheet2").Range("C5").Cells(i, 1).Value = "Not Found"
Next
End SubHow Does the Code Work?
Sub ExtendNamedRange()
Dim lastRow As Long
Dim ws As Worksheet
Dim namedRange As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set namedRange = ws.Range("A1").CurrentRegion
lastRow = namedRange.Rows.Count
With ws
.Names.Add Name:="db_Sheet", RefersTo:=.Range("A1:G" & lastRow)
End With
End Sub- Remember I told you that we will explain the ExtendNamedRange subroutine later when we introduce it inside the UserForm’s code window? Here, we are essentially extending the named range to the last used row in the database worksheet. The last used row is determined by namedRange.Rows.Count from cell A1. As our data will always be confined to column G, we will extend the named range db_Sheet to the last used row of column G(“A1:G” & lastRow).
Sub DeleteAllPictures()
Dim pic As Shape
For Each pic In ActiveSheet.Shapes
If pic.Type = msoPicture Then
pic.Delete
End If
Next pic
End Sub- When we load a picture of the employee from the database worksheet, before that, we need to delete the already existing picture on the worksheet so that the newly loaded picture doesn’t coincide with the previous picture. By running this subroutine, we loop through each shape in the worksheet and delete those that are msoPicture in type.
Sub Get_Details()
Dim look_up_Value As Variant
Dim look_up_Range As Range
Dim result As Variant
' Set the lookup value
look_up_Value = Worksheets("Sheet2").Range("F5").Value
' Set the lookup range
Set look_up_Range = Worksheets("Sheet1").Range("db_Sheet")
' Perform the VLOOKUP and store the result
For i = 1 To 5
Worksheets("Sheet2").Range("C5").Cells(i, 1).Value _
= Application.WorksheetFunction.VLookup(look_up_Value, look_up_Range, i + 1, False)
Next i
End Sub- Here, using the worksheet function VLOOKUP, the text details are extracted and written on corresponding cells in the C column.
Sub Employee_Pic()
On Error GoTo ErrHandl- Here, we wrote another subroutine named InsertPic. By running the subroutine, we will load the employee’s picture. Firstly, for error handling, we wrote a line that will lead us to ErrHandl if any error occurs while running the code.
-
Call DeleteAllPictures
Here we call the DeleteAllPictures subroutine to delete any existing pictures.
Dim picName As String
Dim picHeight As Double
Dim picWidth As Double
picName = "Pic" & Range("F5").Value- Here, Range(“F5”).Value is actually the Employee Number(Emp No). As the name of the employee’s picture on the database worksheet will be associated with his/her Employee Number (Pic+Emp No), we set the name of the picture, picName, as “Pic” & Range(“F5”).Value.
Sheets("Sheet1").Shapes(picName).Copy
Sheets("Sheet2").Range("C4").PasteSpecial- Here, we first copy the Employee’s image from the database worksheet(Sheet1) and paste it on cell C4 of this worksheet (Sheet2).
Sheets("Sheet2").Shapes(Sheets("Sheet2").Shapes.Count).Name = picNameNamed the pasted picture in Sheet2 with the original name from Sheet1.
picHeight = Sheets("Sheet2").Shapes(picName).Height
picWidth = Sheets("Sheet2").Shapes(picName).Width
aspect_Ratio = picWidth / picHeight- Determining the actual picture width, height and aspect ratio.
picHeight = Sheets("Sheet2").Range("C4").RowHeight - 5
picWidth = aspect_Ratio * picHeight- Adjusting the picture height and width with the cell C4 height.
Sheets("Sheet2").Shapes(picName).Height = picHeight
Sheets("Sheet2").Shapes(picName).Width = picWidth- Setting new determined height and width to the inserted picture.
Cell_Height = Sheets("Sheet2").Range("C4").Height
Cell_Width = Sheets("Sheet2").Range("C4").Width
With Sheets("Sheet2").Shapes(picName)
.Top = Sheets("Sheet2").Range("C4").Top + (Cell_Height / 2) - (.Height / 2)
.Left = Sheets("Sheet2").Range("C4").Left + (Cell_Width / 2) - (.Width / 2)
End With- Make the picture Center and Middle aligned inside the cell, then exit the subroutine.
Call Get_Details
Exit Sub- Calling the Get_Details subroutine to extract the text details.
ErrHandl:
MsgBox "No Data Found of this employee"
For i = 1 To 5
Worksheets("Sheet2").Range("C5").Cells(i, 1).Value = "Not Found"
Next
End SubIf the employee ID is not found, a MsgBox will tell the user that “No Data Found of this employee“. On the cells, it will be written: “Not Found”.
- Now, for our convenience, we insert a button and then assign the Employee_Pic subroutine to the button so that we can run the code from the worksheet.
- Now, if we click on the button, we will get the details of the employee.
In this way, we can get all the provided details of an employee, including his or her image, by only using his/her Employee Number. I hope these steps have given you enough knowledge to create a database in Excel with pictures.
Read More: How to Create a Searchable Database in Excel
Things to Remember
- The types and number of information items that need to be stored in a database vastly depend on the context. So, you need to modify the layout of the database sheet, UserForm, and search sheet according to your needs.
- You also need to modify the codes accordingly if you make changes to the worksheet.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. We used Microsoft Excel 365 to explain the steps.
Related Articles
- How to Create a Database with Form in Excel
- How to Maintain Customer Database in Excel
- How to Create a Database That Updates Automatically in Excel
- How to Create a Membership Database in Excel
<< Go Back To Database in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
in the private sub UserForm_Activate() I get a byref argument type mismatch error to do with the variable database how do I fix this
Hello Ravi
Thanks for visiting our blog and posting your problem. The ByRef argument type mismatch error typically occurs when the data types of variables are passed to a subroutine or function mismatch. In this case, the issue seems to be with the variable database in the UserForm_Activate() event.
To fix the ByRef argument type mismatch error:
Regards
ExcelDemy
why do I get a byref error in my UserForm_Activate for my database variable
Hello Nirmani
Thanks for reaching out and sharing your problem. You passed an argument that could not be manipulated into the type expected; the ByRef error happens. For example, when you try to supply an Integer variable when a Long is required.
If you encounter a ByRef error related to your database variable, it could be due to how you pass the worksheet object reference. Ensure that the data types of the database variable matches the expected data type in the subroutine.
When the UserForm_Activate event triggers, pass the correct worksheet reference. Double-check that the worksheet name you’re passing matches the actual name of the worksheet in your Excel file.
Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy