Navigate Database Automatically

How to navigate database in an Excel worksheet automatically using VBA. Watch the video below:

Watch this video on YouTube.

Here’s the complete VBA code for the different navigation buttons and the othe command buttons like Search, AddData, etc.

  1. VBA Code for the ‘Next Record’ button:Sub goToNextRecord()
    Dim currentRow As Long, firstRow As Long, lastRow As Long, nextRow As Long
    lastRow = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row’MsgBox lastRow
    firstRow = 13
    currentRow = Cells(4, 1)If currentRow >= firstRow And currentRow < lastRow Then
    currentRow = currentRow + 1
    Range(“E2”) = Cells(currentRow, 3)
    Range(“E3”) = Cells(currentRow, 4)
    Range(“E4”) = Cells(currentRow, 5)
    Range(“E5”) = Cells(currentRow, 6)
    Range(“E6”) = Cells(currentRow, 7)
    Range(“E7”) = Cells(currentRow, 8)
    Range(“E8”) = Cells(currentRow, 9)End IfCells(4, 1) = currentRowIf currentRow = lastRow Then
    MsgBox “You are now in the last row!”
    End If

    End Sub

  2. VBA code for the ‘Back’ record navigation button;Sub goBack()
    Dim currentRow As Long, firstRow As Long, lastRow As Long, nextRow As Long
    lastRow = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row’MsgBox lastRow
    firstRow = 13
    currentRow = Cells(4, 1)If currentRow > firstRow And currentRow <= lastRow Then
    currentRow = currentRow – 1
    Range(“E2”) = Cells(currentRow, 3)
    Range(“E3”) = Cells(currentRow, 4)
    Range(“E4”) = Cells(currentRow, 5)
    Range(“E5”) = Cells(currentRow, 6)
    Range(“E6”) = Cells(currentRow, 7)
    Range(“E7”) = Cells(currentRow, 8)
    Range(“E8”) = Cells(currentRow, 9)End IfCells(4, 1) = currentRowIf currentRow = firstRow Then
    MsgBox “You are now in the first row!”
    End If
    End Sub
  3. VBA code for the button to go to the first record:Sub goToFirstRow()
    Dim firstRow As Long
    firstRow = 13Range(“E2”) = Cells(firstRow, 3)
    Range(“E3”) = Cells(firstRow, 4)
    Range(“E4”) = Cells(firstRow, 5)
    Range(“E5”) = Cells(firstRow, 6)
    Range(“E6”) = Cells(firstRow, 7)
    Range(“E7”) = Cells(firstRow, 8)
    Range(“E8”) = Cells(firstRow, 9)
    End Sub
  4. How to code the command button to go to the last row:Sub goToLastRow()
    Dim lastRow As Long
    lastRow = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).RowRange(“E2”) = Cells(lastRow, 3)
    Range(“E3”) = Cells(lastRow, 4)
    Range(“E4”) = Cells(lastRow, 5)
    Range(“E5”) = Cells(lastRow, 6)
    Range(“E6”) = Cells(lastRow, 7)
    Range(“E7”) = Cells(lastRow, 8)
    Range(“E8”) = Cells(lastRow, 9)End Sub
  5. Complete VBA code to add data to the database and avoid a duplicate entry:Sub addData()
    Dim i As Long, lastRow As Long, nextBlankRow As Long
    lastRow = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).RownextBlankRow = lastRow + 1If Range(“E2”) = “” Then
    MsgBox “You didn’t enter the first name of the customer!”
    Range(“E2”).Select
    Exit Sub
    End IfIf Range(“E3”) = “” Then
    MsgBox “You didn’t enter the last name of the customer!”
    Range(“E3”).Select
    Exit Sub
    End IfCells(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”)
    ‘MsgBox nextBlankRow
    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
    LoopEnd Sub
  6. Code for the ‘Clear’ button:┬áSub resetData()
    Range(“E2:E8”).ClearContents
    End Sub
  7. VBA code for the ‘Search’ button:Sub checkExistingData()Dim i As Long, lastRow As LonglastRow = Cells.Find(What:=”*”, _
    After:=Range(“A1”), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).RowIf Range(“E2”) = “” Then
    MsgBox “You didn’t enter the first name of the customer!”
    Range(“E2”).Select
    Exit Sub
    End IfIf Range(“E3”) = “” Then
    MsgBox “You didn’t enter the last name of the customer!”
    Range(“E3”).Select
    Exit Sub
    End IfFor 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)
    Cells(4, 1) = i
    Exit Sub
    End If
    Next i

    MsgBox lastRow

    MsgBox “Record doesn’t exist”

    End Sub

  8. How to code the ‘Display Image’ button:
  9. Sub displayPic()
    Application.ScreenUpdating = False
    Dim myObj
    Dim Pictur
    Set myObj = ActiveSheet.DrawingObjects
    For Each Pictur In myObj
    If Left(Pictur.Name, 7) = “Picture” Then
    Pictur.Select
    Pictur.Delete
    End If
    NextDim EmployeeName As String, T As StringmyDir = “C:\Users\takyar\Pictures\employees\”
    EmployeeName = Range(“E2”) & Range(“E3”)
    T = “.jpg”‘Range(“F2”).Value = EmployeeName
    On Error GoTo errormessage:
    ActiveSheet.Shapes.AddPicture Filename:=myDir & EmployeeName & T, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=420, Top:=15, Width:=50, Height:=50errormessage:
    If Err.Number = 1004 Then
    MsgBox “File does not exist.” & vbCrLf & “Check the name of the employee!”
    Range(“E2”).Value = “”
    Range(“E3”).Value = “”
    End IfApplication.ScreenUpdating = True

    End Sub

  10. How to code the ‘Update’ records button:Sub updateRecord()
    Dim reply As String
    Dim currentRow As Longreply = InputBox(“Are you sure you wish to update the record? Type y to update”)
    If reply = “y” Then
    currentRow = Cells(4, 1)
    Cells(currentRow, 3) = Range(“E2”)
    Cells(currentRow, 4) = Range(“E3”)
    Cells(currentRow, 5) = Range(“E4”)
    Cells(currentRow, 6) = Range(“E5”)
    Cells(currentRow, 7) = Range(“E6”)
    Cells(currentRow, 8) = Range(“E7”)
    Cells(currentRow, 9) = Range(“E8”)
    Else
    Exit Sub
    Range(“E2”).SelectEnd IfEnd Sub
  11. VBA code for the ‘Delete’ record button:Sub deleteRecord()
    Dim reply As String
    reply = InputBox(“Are you sure you wish to delete the record? This action cannot be undone! Delete anyway? Then type y”)
    If reply = “y” Then
    currentRow = Cells(4, 1)
    Cells(currentRow, 1).EntireRow.Delete
    Else
    Range(“E2”).Select
    Exit Sub
    End If
    End Sub

Important: You will notice that many of the variables used in different modules are repeated. To make coding simpler we can declare these variables as module level variables or public level variables. Such variables can be accessed by all the modules in the project since they retain their values in between runs of our code. The concept and use of global variables is explained in another example.

Downlad a sample file:

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

5 thoughts on “Navigate Database Automatically”

  1. I need help with the code
    Private Sub Workbook_Open()
    Dim i, LastRow
    LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
    Sheets(“Sheet6”).Range(“A2:F10”).ClearContents
    For i = 2 To LastRow
    If Sheets(“Sheet1”).Cells(i, “C”).Value = “M” Then
    Sheets(“Sheet1”).Cells(i, “C”).EntireRow.Copy Destination:=Sheets(“Sheet6”).Range(“A” & Rows.Count).End(xlUp).Offset(1)
    End If
    Next i
    End Sub

    This Code is good to search only one tab
    What If I have mutiple tabs or want to use the same code for entire workbook carrying different worksheets

  2. Dear Dinesh,
    Where are you based, I would like to contact you regarding a project and would like you to write a program me.

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.