Advanced UserForm 

Home More Excel (VBA) Training Videos
     Webhosting by Godaddy     MS Excel Books








How to created an advanced userform 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:

Advanced UserForm
Watch the video on YouTube
References
Placing Images on UserForm
Getting Images onto UserForm Picture Holder from any Folder
Range.Find Method
     Webhosting by Godaddy     MS Excel Books
Home More Excel (VBA) Training Videos