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:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

17 thoughts on “Create Database in Excel Quickly and Easily”

  1. Dear Mr.Dines
    Thx for share yours knowledge.
    I already insert your source code modul 1.. there is note : compile error’ syntax error’.
    World you please help.
    Warm regards
    Rt

  2. Hello Sir,
    i pasted your code, then there were some errors, i corrected msgbox errors then clear content errors, then now i am stuck at,

    its is giving error Range of Object Global Failed,
    and when i click debug , the last row vba code is highlighted.
    Can you teach me where i am going wrong.
    I have replicated the sheet as per your instructions in the sheet but stuck here

    1. Or if you can share a working vba excel file, of this video,
      i will compare and see where i am going wrong.
      Let know how you can help??

  3. I got errors also, but here’s how I resolved them: after pasting, I did an Edit > Replace. I replaced all the left- and right- double quote characters with regular double quotes, and replaced all the left- and right- single quote characters with regular single quotes. Then the “*” looked like ” * “, so I changed them to “*”.

  4. Hello,

    First, I would like to thankyou for teaching and providing information in Excel as it is a great help in my work. However, as Easy it is to adjust someparts to my liking I am having difficulty in making the Macro enter the data in a Different sheet than in the first sheet I’ve entered.

    Is there a way for the LastRow= to enter the Data in a different sheet or should I start from scratch?

    Thank you again for the wonderful teaching and I hope to hear from you soon.

    Sincerely,
    Sy Noble

  5. Tried to make my own Macro book and it is not working accordingly, do you mind looking at it for some input?

  6. Hi Sir,
    I really loved this tutorial on how to create database in excel. Learned something new from your post.
    Keep sharing such kind of informative post.
    Bookmarked!

  7. This post is absolutely useful for everyone while creating database in excel. Really you write in such way that it makes readable for us to understand everything.
    Such an useful content.
    Thanks.
    Keep it up
    Regards~
    Nuncia

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.