Advanced User Form

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

Advanced User Form

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)

7 thoughts on “Advanced User Form

  1. Pingback: Automatically send email to users queries in Excel with VBA | Excel VBA Training Videos

  2. Adad M. Cherif

    Hello Dr. Kumar Takyar

    The code work propely except for the image section, I don’t know wearther I write the file path or only “fPath” . In other words, I did not succeed where to put the path related to jpg images . I wrote file path in fPath = ThisWorkbook.Path & “\” , but nothing happened.

    All the best

    Prof Adad M. Cherif , Algeria

    Reply
  3. Pingback: Create Manage Database in Excel with User Form | Excel VBA Training Videos

  4. JOHN

    Dear Sir, Your teaching is so amazing that any one can understand the great and difficult things easily. I am so happy with your teachings. I have created a userform using the codes given for advanced userform. It has the details of Student name, class, Father name, mobile no, photo etc. I would like to search the data when I enter the student name and class in the text box and click search button. Also I want to delete certain data using delete button. Could you please help me to do search and delete as per the same code given for Advanced Userform?

    Reply
  5. Pingback: How to display hidden worksheet based on password | Excel VBA Training Videos

  6. Tim

    This is very nice and it would be perfect if I could the images part to work. Currently it only works with one image (nopic). I visited the link you provided at the end of your video, but that wasn’t any help. Your help in the will be greatly appreciated. Thank you.

    Reply
  7. Pingback: Get Data from Random Check Boxes into Excel Worksheet | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *