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: