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

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

3 thoughts on “Create UserForms with Navigation Buttons”

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

Leave a Reply

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