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)

Advanced Excel for Productivity

17 thoughts on “Advanced User Form”

  1. 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

    1. Dim currentrow As Long
      Dim fPath As String ‘Add this row

      Private Sub cmdGetNext_Click()
      …..
      With NameFound
      On Error Resume Next
      imgData.Picture = LoadPicture(fPath & “\nopic.jpg”) ‘Add “\”
      imgData.Picture = LoadPicture(fPath & txtFirstName.Text & “.jpg”)
      End With
      End With

    2. Dim currentrow As Long
      Dim fPath As String ‘Add this row

      Private Sub cmdGetNext_Click()
      …..
      With NameFound
      On Error Resume Next
      imgData.Picture = LoadPicture(fPath & “\nopic.jpg”) ‘Add “\”
      imgData.Picture = LoadPicture(fPath & txtFirstName.Text & “.jpg”)
      End With
      End With

      Replace this row: fPath = ThisWorkbook.Path & “\” BY fPath = Application.ActiveWorkbook.Path & “\”

  2. 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?

  3. 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.

  4. Hi,
    Could you do a video on how to add data from worksheet using a userform with Combobox1 and 2
    and couple of text textbox with time and percentage. wherein I have two sheet as sheet1 and sheet2. Sheet1 have the combobox1 and combox2 data and in sheet2 have all the data which need to be populated in the textbox once the combination of both combobox selected.

    Thanks in advance
    Akbar Alam

  5. Thanks for ur teaching user form in excel sheet. While coding I faced a problem. Initially I put coding lebels & text. It is oK upto get data from worksheet. Now how I’ll create send next data and get previous data. Pls help.
    Regards
    J K Nag

  6. Your video really helps me a lot in creating a userform for our masterlist…but, I have some problem concerning the when when we need to add details to the next row..always got error. And also, if u could make a video about how to transfer data from optionbutton..
    Thanks in advance foe ur help.

Leave a Reply

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