In this last (fourth video) we describe the process of viewing previous and next records on a userform and how to update records on the Excel worksheet via a userform quickly and easily. The code for the ‘end’ command button is also given. It’s a single line of VBA code!

Private Sub cmdUpdate_Click()
Dim title As String, fname As String, lname As String, email As String, dob As Date

title = cboTitle.Text
Cells(currentrow, 1) = title
fname = txtFname.Text
Cells(currentrow, 2) = fname
lname = txtLname.Text
Cells(currentrow, 3) = lname
phone = txtPhone.Text
Cells(currentrow, 4) = phone
email = txtEmail.Text
Cells(currentrow, 5) = email
dob = txtDob.Text
Cells(currentrow, 6) = dob

If chkBclub Then
Worksheets(“Book Club”).Select
erow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = cboTitle.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 3) = txtLname.Text
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If

If chkTennis Then
Worksheets(“Tennis”).Select
erow = ActiveSheet.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
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If

If chkGuitar Then
Worksheets(“Guitar”).Select
erow = ActiveSheet.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
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If

If chkKeyboard Then
Worksheets(“Keyboard”).Select
erow = ActiveSheet.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
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If

If chkCvisits Then
Worksheets(“Care Visits”).Select
erow = ActiveSheet.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
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If

Worksheets(“Main Database”).Select
End Sub

Of course, we can update the data in all the other activity worksheets by adopting a similar strategy as adopted for the ‘Main Database’ worksheet. We can loop through all the worksheets bu using a nested looping process as described below.

View the training video to quickly and easily understand how to view next and previous records in an Excel worksheet with userform and how to update multiple worksheet records with userform:

View the video on YouTube

Private Sub cmdPrevious_Click()
Dim q As Integer
currentrow = currentrow – 1
If currentrow > 1 Then
cboTitle.Value = “”
txtFname.Text = “”
txtLname.Text = “”
txtPhone.Text = “”
txtEmail.Text = “”
txtDob.Text = “”
chkBclub = False
chkTennis = False
chkGuitar = False
chkKeyboard = False
chkCvisits = False

cboTitle.Text = Cells(currentrow, 1)
txtFname.Text = Cells(currentrow, 2)
txtLname.Text = Cells(currentrow, 3)
txtPhone.Text = Cells(currentrow, 4)
txtEmail.Text = Cells(currentrow, 5)
txtDob.Text = Cells(currentrow, 6)

fname = txtFname.Text
lname = txtLname.Text

For q = 1 To Sheets.count
lastrow = Sheets(q).Range(“A” & Rows.count).End(xlUp).Row
Dim x As Long
x = 2
For x = 2 To lastrow
If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Book Club” Then

UserForm1.chkBclub = True

End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Tennis” Then

UserForm1.chkTennis = True
End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Guitar” Then
UserForm1.chkGuitar = True
End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “KeyBoard” Then
UserForm1.chkKeyboard = True
End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Care Visits” Then
UserForm1.chkCvisits = True
End If

Next x

Next q

ElseIf currentrow = 1 Then
MsgBox “Now you are in the header row!”, vbCritical
currentrow = currentrow + 1

End If

End Sub

Private Sub cmdNext_Click()
Dim x As Long, lastrow As Long
Dim q As Integer
lastrow = Sheets(“Main Database”).Range(“A” & Rows.count).End(xlUp).Row

If currentrow = lastrow Then
MsgBox “you are viewing the last row of data”, vbCritical
Else
cboTitle.Value = “”
txtFname.Text = “”
txtLname.Text = “”
txtPhone.Text = “”
txtEmail.Text = “”
txtDob.Text = “”
chkBclub = False
chkTennis = False
chkGuitar = False
chkKeyboard = False
chkCvisits = False

currentrow = currentrow + 1
cboTitle.Text = Cells(currentrow, 1)
txtFname.Text = Cells(currentrow, 2)
txtLname.Text = Cells(currentrow, 3)
txtPhone.Text = Cells(currentrow, 4)
txtEmail.Text = Cells(currentrow, 5)
txtDob.Text = Cells(currentrow, 6)

Dim fname As String, lname As String
fname = txtFname.Text
lname = txtLname.Text

For q = 1 To Sheets.count

x = 2
lastrow = Sheets(q).Range(“A” & Rows.count).End(xlUp).Row
For x = 2 To lastrow

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Book Club” Then

UserForm1.chkBclub = True

End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Tennis” Then

UserForm1.chkTennis = True

End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Guitar” Then
UserForm1.chkGuitar = True
End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “KeyBoard” Then
UserForm1.chkKeyboard = True
End If

If fname = Sheets(q).Cells(x, 2) And lname = Sheets(q).Cells(x, 3) And Sheets(q).Name = “Care Visits” Then
UserForm1.chkCvisits = True
End If

Next x

Next q

End If

End Sub

Private Sub cmdEnd_Click()
Unload Me
End Sub

In the last 4 videos on database and customer relationship management you would have noticed that a lot of VBA code is repeated. You can avoid this situation if you create a separate macro(s) for such VBA code and then call these macros from inside another procedure as shown in this training video.

Download a sample file by clicking on the Excel icon: