Create UserForms with Navigation Buttons

How to create user-forms with navigation buttons coded in VBA to loop through Excel data. Earlier we had demonstrated how create and manage databases in Excel with user forms. But many people were not able to implement the concept. Most notably many found the concept of global variables difficult to use and many just forgot to declare the global variable at the top of the VBA code. Once you understand the global variable and how to use the correct sequence in VBA coding of the ‘back’ and ‘next’ navigation buttons, you’ll be able to create other buttons like ‘update’ and ‘clear data’. This will make the managing of databases in Excel with user-forms easier.

Watch the video below:

 

Watch this video on YouTube.

Here’s the complete VBA code to create user-forms with navigation buttons:

Dim currentrow As Long  declare the global variable right at the top

 

Private Sub CommandButton2_Click()
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

If currentrow = lastrow Then
MsgBox “you are in the last row! No more data.”
Exit Sub
End If

currentrow = currentrow + 1

TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)
End Sub

Private Sub CommandButton3_Click()
If currentrow = 2 Then
MsgBox “You are in the first row!”
Exit Sub
End If

currentrow = currentrow – 1

TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)
End Sub

Private Sub UserForm_Initialize()
currentrow = 2
TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)

End Sub

 

Further reference:

Advanced User-Form

 

3 thoughts on “Create UserForms with Navigation Buttons

  1. anil mamidwar

    hi sir , i found your utube videos very helpful . i am a trainer in a small firm where i teach them about excel .
    Need your help . can you provide me your email id to discuss more

    Reply
  2. Pingback: Search for Data In Excel Database with UserForm | Excel VBA Training Online

Leave a Reply

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