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: