Create Database in Excel Quickly and Easily

How to create a database in Excel worksheet quickly and easily by using shapes assigned to macros.

Today we’ll create a database using the worksheet cells as a form. We’ll create three command buttons using a rectangular shape. These command button shapes will be assigned to the relevant macros.

The first command button shape has the caption (text) ‘Add Record’. The macro attached to this button does the following: We fill in the data in cells E2 to E8 and click ‘Add Record’ and the data is transferred to the next blank row in the same sheet a few rows below. Now we wish to ensure that the data entered is not already in the database. We use a nested loop to check for duplicates and if the last entry is a duplicate based on the first name and the last name of the customer, we remove it from the database. Of course, we can compare the mobile number or an ID and ensure even further that no duplicate entry has been made. I would suggest that we practice this idea to get more proficiency in VBA programming.

The second command button shape with the text (caption) ‘Clear Data’ removes the data from the worksheet cells E2 to E8 so that we can make a new entry.

The third command button shape with the caption ‘Existing Record’ populates the cells E4 to E8 when you enter the first name and the last name of the customer in cell E2 and E3 respectively. If you don’t  enter the first name and the last name in the cells E2 and E3, the macro gives out a warning message and places the cursor in the relevant cell so that we can enter the appropriate data.

Watch the video below before studying the VBA code for the creation of the database:

Watch this video on YouTube.

Here’s the complete code to create a database in an Excel worksheet without a user-form:

(1) Code attached to the button Add Record

Sub addData()

‘first we declare a few variables
Dim i As Long, lastrow As Long, nextBlankRow As Long

‘lastrow based on Find
lastrow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

nextBlankRow = lastrow + 1

‘ we ensure that the user enters the first name

If Range(“E2”) = “” Then
MsgBox “You didn’t enter the first name of the customer!”
Range(“E2”).Select
Exit Sub
End If

‘we ensure that the user enters the last name

If Range(“E3”) = “” Then
MsgBox “You didn’t enter the last name of the customer!”
Range(“E3”).Select
Exit Sub
End If

‘ we transfer data fro cells E2 to E8 to the worksheet below

Cells(nextBlankRow, 3) = Range(“E2”)
Cells(nextBlankRow, 4) = Range(“E3”)
Cells(nextBlankRow, 5) = Range(“E4”)
Cells(nextBlankRow, 6) = Range(“E5”)
Cells(nextBlankRow, 7) = Range(“E6”)
Cells(nextBlankRow, 8) = Range(“E7”)
Cells(nextBlankRow, 9) = Range(“E8”)

‘Now we check for duplicates using a nested ‘do while’ loop

‘ and delete the data if it is a duplicate entry

Dim p As Long, q As Long
p = 13
q = p + 1
Do While Cells(p, 3) <> “”
Do While Cells(q, 3) <> “”
If Cells(p, 3) = Cells(q, 3) And Cells(p, 4) = Cells(q, 4) Then
MsgBox “Duplicate Data! Will be removed from database!”
Range(Cells(q, 3), Cells(q, 9)).ClearContents
Else
q = q + 1
End If
Loop
p = p + 1
q = p + 1
Loop

End Sub

(2) Code for the ‘Clear Data’ button

Sub resetData()
Range(“E2:E8″).ClearContents
End Sub

(3) Code for the ‘Existing Record’ button

Sub checkExistingData()

Dim i As Long, lastrow As Long

lastrow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

If Range(“E2”) = “” Then
MsgBox “You didn’t enter the first name of the customer!”
Range(“E2”).Select
Exit Sub
End If

If Range(“E3”) = “” Then
MsgBox “You didn’t enter the last name of the customer!”
Range(“E3”).Select
Exit Sub
End If

‘looping process to check for an existing record based on user’s entries in cells E2 and E3

‘the user enters the first name in E2 and last name in E3

‘of course we can also check against other details like mobile number or ID, if used

For i = 13 To lastrow

If Cells(i, 3) = Range(“E2”) And Cells(i, 4) = Range(“E3”) Then
Range(“E4”) = Cells(i, 5)
Range(“E5”) = Cells(i, 6)
Range(“E6”) = Cells(i, 7)
Range(“E7”) = Cells(i, 8)
Range(“E8”) = Cells(i, 9)
Exit Sub
End If
Next i

MsgBox “Record doesn’t exist”

End Sub

Further Reading

Databases in Excel with User-Form

 
Download a sample file for practice: