In this article, I am going to show how to create a database in Excel with pictures. Even though MS Excel is not an ideal application to build a database, we can manage to create a simple database with pictures. Here, I will show you a step-by-step procedure on how we can build an employee database that will contain all the necessary details, such as name, ID number, phone, address, etc. It will also contain an image of the employees. We will use a UserForm to enter information into the database. We will also have a search system that will allow us to get all the information about an employee by using the employee ID. So, let’s get started.
Watch Video – Create a Database in Excel with Pictures
How to Create a Database in Excel with Pictures: with Easy Steps
I break down the whole procedure into five steps. Let’s explore those steps to see how to create a database in Excel with pictures.
Step 1: Create a Header Row in a Worksheet
Here, we first need to create a header row in a new worksheet where there will be a column for each item of information.
Here, I have taken 7 columns (Emp No, Name, Address, Phone, Designation, DOB, and Photo) in a worksheet named Sheet1. Eventually, we will store all the details of employees on this worksheet. Now we need to insert that information in a structured way. We can manually enter the data for each employee directly on the worksheet. Alternatively, we can use a UserForm to do that. Here, we will use a VBA UserForm.
Step 2: Design a UserForm for Entering Data into the Database
Now, we will design a UserForm named Database_Entry_Form that will have all the fields necessary to hold the data of an employee. In my case, the UserForm looks like this.
In the UserForm, I took 6 TextBox and their corresponding Label. I also added an Image component to hold the picture of the Employee. Finally, four CommandButton were also added. For beautification, I changed the default background color of some components. The name of each component is given in the following picture. These names will be required while coding.
Now, we need to write some codes so that when we enter data in the UserForm named Database_Entry_Form, the data is stored in the database worksheet serially.
Step 3: Writing VBA Code to Store Data in Database
First of all, we need to write some codes so that whenever we click the Add Image button, a window will open up asking us to select a picture of the employee. To do that, double-click on the Add Image button. As a result, the code window will open. Now, write the following code there.
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 Sub📌 How Does the Code Work?
imagePath = Application.GetOpenFilename("Image Files (*.bmp;*.jpg;*.gif;*.png),*.bmp;*.jpg;*.gif;*.png", , "Select the Image of employee")- Here we use the Application.GetOpenFilename method to open a File Dialogue box. This dialogue box will have the title Select the Image of employee and will only display the *.bmp;*.jpg;*.gif;*.png” type image files. The file path of the selected image will be stored in a variable named imagePath.
If imagePath = False Then Exit Sub- If no image is selected, then the sub will exit.
img_Emp.Picture = LoadPicture(imagePath)- On the other hand, if any valid image file is selected, it will be loaded into the img_Emp component of the UserForm.
- Now, we need to assign a code to the Delete button (cmdDel) so that whenever we click on the Delete button, it will remove the loaded image from the UserForm. To do that, double-click on the Delete button and paste the following code.
Private Sub cmdDel_Click()
img_Emp.Picture = Nothing
End SubNow, we will assign a code to the Save button (cmdSave_Click) so that whenever we click on the Save button, all the entered data can be transferred into the Database worksheet. Before that, we also need to write some other subs as well to support the cmdSave_Click.
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 Sub📌 How Does the Code Work?
Option Explicit:
Dim database As Worksheet
Dim imagePath As Variant
Dim db_range As String
Dim r As Long- Here, four variables are declared that will be used in different procedures eventually.
Public Function lastRow(ws As Worksheet) As Long
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
End Function- Here, the function lastRow is defined. It takes a worksheet as an argument and determines the last used rows of that sheet using End(xlUp).Row method, and returns the row number.
Private Sub UserForm_Activate()
Set database = Worksheets("Sheet1")
r = lastRow(database) + 1
db_range = "A1:G" & r
End Sub- When the Userform is activated, this code is run to assign the database worksheet to the database variable.
Private Sub cmdSave_Click()
On Error Resume Next
r = lastRow(database) + 1- This code will only run when the cmdSav button is clicked. On Error Resume Next, the code can proceed further even if any error occurs. The last used row of the database is determined using a custom function, lastRow. The function returns the last used row number. Then 1 is added to the number so that we can determine the first empty row number. Finally, the number is assigned to a variable named r.
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- Here, the text values in different TextBoxes in the Userform are stored in their corresponding cells in the database worksheet.
If (IsNull(img_Emp.Picture)) Then
'do nothing- Here, first, it is checked whether the image component img_Emp contains an image or not. If it doesn’t contain any image, we can not load any image to the database; hence, no task is performed.
Else
Dim selectedCell As Range
Dim imgHeight As Long
Dim imgWidth As Long
Dim imgRatio As Double
Dim img As Shape- If the image component img_Emp contains any image, then the subsequent code will run. Here, we first define some variables which will be used subsequently.
Set selectedCell = database.Range("A1").Cells(r, 7)- Here, the cell in the database worksheet where the image will be stored is assigned to a variable named selectedCell.
imgHeight = img_Emp.Picture.Height
imgWidth = img_Emp.Picture.Width
imgRatio = imgHeight / imgWidth
imgHeight = 40
imgWidth = imgHeight / imgRatio- Here, we first determine the height and width of the image that has been loaded in img_Emp. Then, its aspect ratio is determined. After that, we take the height to be 40 (imgHeight) and determine the corresponding width (imgWidth) of the image while maintaining the same aspect ratio.
EntireRow.RowHeight = imgHeight + 5- Increasing the row height of the cell where the image will be stored so that the image can easily fit in the 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)- Here, the image from the UserForm is loaded to the database worksheet using its folder path, imagePath, which we got from the cmdAddUpdate_Click subroutine.
Name = "Pic" & Database_Entry_Form.txtEmpNo.Value- The inserted image name is changed to something related to the employee ID so that it can be called again when necessary by the employee ID number.
Call ExtendNamedRange- Later, when we will look for employee data in the database using Employee ID, we will need to specify the range within which we need to look. For this region, we not only need to name the used range for data storage but also, after entering a new entry, we need to extend the named range. To do that, I have written a separate subroutine named ExtendNamedRange. The code of this subroutine will be explained later when we will see how we will search the info of an employee using his/her employee ID.
We also need to write some code so that when we click the Close button, it will close down the UserForm.
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
As we are ready to use the UserForm named Database_Entry_Form to enter data into the database worksheet, let’s run the UserForm. To do that, follow the steps below.
Steps:
- Click on the UserForm then click the Run button on the top.
- As a result, the UserForm will open up. Now, enter all the necessary data in the UserForm. To enter the image of the employee, click on the Add Image button.
- As a result, a window will open up and ask you to select the image of the employee from the File Explorer. Here, we need to select any image and then click on Open.
- As a result, the selected image will load into the UserForm. To save the employee’s information, we need to click on the Save button on the UserForm. As a result, the inserted information along with the image will be stored in the database worksheet.
- As a result, the information will safely be stored inside the database worksheet.
- In this way, we can store as much employee data as we want. Here, I have entered a total of 5 employees’ data. Then I clicked on the Close button to close the UserForm.
- Finally, my database looks like this.
So, our database is ready, which contains pictures as well. But in Excel, we cannot have all the functionalities of a database that a dedicated database software provides such as different data analysis reporting, queries, etc. However, to enhance the functionality of this database, I will create a search function in another sheet where we can search the details of an employee using the Employee ID.
Step 5: Creating Database Search System
So, we will create a search system in another sheet that looks like this.
Here, we only need to enter the Employee Number (Emp No) in cell F5, and we will get the employee’s other information on the left side.
To extract the text information of an employee, we need to write some formula in cell C5:C9, and to get the picture of the employee, we need to write some code in a new module and then assign a macro to the button Get Photo of Employee. To do all of these things, follow the steps below.
Steps:
- Open a new module and paste the following 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 Sub📌 How 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 this practice workbook to exercise while you are reading this article. We used Microsoft Excel 365 to explain the steps.
Conclusion
That is the end of this article regarding how to create a database in Excel with pictures. If you find this article helpful, please share it with your friends. Moreover, do let us know if you have any further queries.
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