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