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.
- 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 IfEnd Sub
- 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 - 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 - 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 - 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 - Code for the ‘Clear’ button: Sub resetData()
Range(“E2:E8”).ClearContents
End Sub - 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 lastRowIf 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 iMsgBox lastRow
MsgBox “Record doesn’t exist”
End Sub
- How to code the ‘Display Image’ button:
- 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 = TrueEnd Sub
- 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 - 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:
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
Sir I want to do excel file open only two time as expire date set using vb code please help
Dear Dinesh,
Where are you based, I would like to contact you regarding a project and would like you to write a program me.