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: