How to create an advanced user form in MS Excel with next and previous record displaying command buttons, including picture displays.

Advanced User Form

The complete VBA code is given below:

Dim currentrow As Long

Private Sub cmdGetNext_Click()
Dim NameFound As Range
fPath = ThisWorkbook.Path & “\”
Range(“A2”).Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
currentrow = currentrow + 1
If currentrow = lastrow + 1 Then
currentrow = lastrow
MsgBox “You have reached the last row!”
End If

With Cells(currentrow, 1)
txtFirstName.Text = Cells(currentrow, 1).Value
Set NameFound = .Find(txtFirstName.Text)

With NameFound
On Error Resume Next
imgData.Picture = LoadPicture(fPath & “nopic.jpg”)
imgData.Picture = LoadPicture(fPath & txtFirstName.Text & “.jpg”)
End With
End With

txtFirstName.Text = Cells(currentrow, 1).Value
txtLastName.Text = Cells(currentrow, 2).Value
txtMobile.Text = Cells(currentrow, 3).Value

End Sub

Private Sub cmdPreviousData_Click()
Dim NameFound As Range
fPath = ThisWorkbook.Path & “\”
currentrow = currentrow – 1
If currentrow > 1 Then
txtFirstName.Text = Cells(currentrow, 1).Value
txtLastName.Text = Cells(currentrow, 2).Value
txtMobile.Text = Cells(currentrow, 3).Value

With Cells(currentrow, 1)
txtFirstName.Text = Cells(currentrow, 1).Value
Set NameFound = .Find(txtFirstName.Text)

With NameFound
On Error Resume Next
imgData.Picture = LoadPicture(fPath & “nopic.jpg”)
imgData.Picture = LoadPicture(fPath & txtFirstName.Text & “.jpg”)
End With

End With

ElseIf currentrow = 1 Then
MsgBox “This is your first record!”
currentrow = currentrow + 1
End If

End Sub

Private Sub cmdSend_Click()
Range(“A2”).Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
‘MsgBox lastrow
Cells(lastrow + 1, 1).Value = txtFirstName.Text
Cells(lastrow + 1, 2).Value = txtLastName.Text
Cells(lastrow + 1, 3).Value = txtMobile.Text
Range(“A2”).Select
txtFirstName.Text = “”
txtLastName.Text = “”
txtMobile.Text = “”
End Sub

Private Sub UserForm_Initialize()
currentrow = 1
If currentrow = 1 Then
MsgBox “You are now in the header row. Click Next to see the first data!”
txtFirstName.Text = “”
txtLastName.Text = “”
txtMobile.Text = “”
End If
End Sub

Watch the Excel VBA training video below for all the details:

Further reading:
How to populate an image control in an Excel user-form with data from a worksheet using VBA
VBA code to load a .jpeg file into an image on a form
Range.Find Method (Excel)

Advanced Excel for Productivity