Update navigate records Excel worksheet with user form

How to update navigate records Excel worksheet with user form. Before you start coding the command buttons enter this line of code right at the top:

Dim currentrow As Long

This line of code defines a variable called ‘currentrow’ and it will be available to all the modules in this workbook.

Now watch the training video before you view the rest of the code:

 

We create our user-form with the following buttons:

  • Clear – Clears the data from the user-form controls and sets the cursor on the combo-box. The VBA code is: Private Sub cmdClear_Click()
    cboTitle.Text = “”
    txtFname = “”
    txtLname = “”
    End Sub
  • Add – Adds records to our Excel worksheet by first locating the next blank row. The VBA code is: Private Sub cmdAdd_Click()
    If cboTitle.Text = “” Then
    MsgBox “Please select a title”
    cboTitle.SetFocus
    Exit Sub
    End If
    If txtFname.Text = “” Then
    MsgBox “Please enter a first name”
    txtFname.SetFocus
    Exit Sub
    End If
    If txtLname.Text = “” Then
    MsgBox “Please enter a last name”
    txtLname.SetFocus
    Exit Sub
    End If
    erow = Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(erow, 1) = cboTitle.Text
    Cells(erow, 2) = txtFname.Text
    Cells(erow, 3) = txtLname.Text
    End Sub
  • Previous button – navigates us to the previous record.  The VBA code is:Private Sub cmdPrevious_Click()
    currentrow = currentrow – 1
    If currentrow > 1 Then
    cboTitle.Text = Cells(currentrow, 1)
    txtFname.Text = Cells(currentrow, 2)
    txtLname.Text = Cells(currentrow, 3)
    End IfIf currentrow = 1 Then
    MsgBox “Now you are in the header row!”, vbCritical
    currentrow = currentrow + 1
    End IfEnd Sub
  • Next button – navigates user to the next record in the Excel worksheet. The VBA code is:Private Sub cmdNext_Click()
    Dim lastrow As Long
    lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).RowIf currentrow = lastrow Then
    MsgBox “you are viewing the last row of data”, vbCritical
    Else
    currentrow = currentrow + 1
    cboTitle.Text = Cells(currentrow, 1)
    txtFname.Text = Cells(currentrow, 2)
    txtLname.Text = Cells(currentrow, 3)
    End IfEnd Sub
  • Update command button – updates the record in the Excel worksheet of the current record. The VBA code: Private Sub cmdUpdate_Click()
    Cells(currentrow, 1) = cboTitle.Text
    Cells(currentrow, 2) = txtFname.Text
    Cells(currentrow, 3) = txtLname.Text
    End Sub
  • VBA code when the user-form initializes:Private Sub UserForm_Initialize()
    currentrow = 2
    cboTitle.List = Array(“Ms”, “Mrs”, “Mr”, “Dr”, “Prof”)
    cboTitle.Text = Cells(currentrow, 1)
    txtFname.Text = Cells(currentrow, 2)
    txtLname.Text = Cells(currentrow, 3)
    cboTitle.SetFocus
    End Sub
  • Visual Basic for Applications code when the workbook is opened: Private Sub Workbook_Open()
    UserForm1.Show
    End Sub

Further reading:

Variable Scope

Database and Customer Relationship Management with Excel

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

6 thoughts on “Update navigate records Excel worksheet with user form”

  1. ahm can we add match destination formula where you will find the value ex associate ID before it add or populate the data.

    Thanks in Advance

  2. IS IT POSSIBLE TO UPDATE DATA OF A CLOSED WORKBOOK WITHOUT OPENING IT.?
    MY COSTING WORKBOOKS ARE LINKED TO ONE DATABASE WORKBOOK.SUDDENLY I GET NOTICE OF PRICE CHANGES OF ITEMS IN THIS DATABASE.CAN I UPDATE THEM WITHOUT REALLY OPENING THIS DATA BASE.? ITEMS ARE RECORDED UNDER DIFFERENT CATEGORIES. PL.HELP
    THANKS FOR YOUR VALUABLE SUPPORT.

  3. I : instance 1 – combobox selected – populated textboxes
    Q : How can i Update combobox navigated records Excel worksheet with user form?

    Thanks

  4. Is it possible to run the next or previous command without using a global variable.?
    Can I run the next or previous command by declaring variable inside the Sub routine only.?

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.