How to Update Excel Worksheet Records with UserForm


The training video on how to update Excel worksheet records with userforms is based on many queries like this:

‘Firstly let me thank you for all your videos on you tube. ¬†Without them I would have been completely lost!

I’ve just watched your Advanced user form – Add data, next, previous and images video and from this have created next and previous command buttons on my user form.

On my user form I need to use next and previous button to display each rows data.  From here I will need to edit in the user form and have a command button to edit the changes to that worksheet row. At present i am only able to add new line at the bottom of the work sheet rather than edit the displayed row.

If you have already done a video about this could you give me the URL or title as I can’t find it!

Many thanks and kind regards’

View the video below and then have a detailed look at the VBA code:


We can update Excel worksheet records with UserForm using appropriately coded command buttons. Before we can perform the update we need to learn how to add records from the UserForm to the Excel worksheet, navigate each record in the worksheet via the userform, be able to find a specific record using criteria and also be able to delete records that are not required any more. Because all these activities can happen quickly and easily via UserForms, the method is quite useful and convenient.

The VBA code to add, find, navigate, update and delete records through userform worksheet interactions is given below:

Dim currentrow As Long

Private Sub cmdAdd_Click()
Dim lastrow As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, “A”).Value = txtFname.Text
Cells(lastrow + 1, “B”).Value = txtLname.Text
End Sub

Private Sub cmdClear_Click()
txtFname.Text = “”
txtLname.Text = “”
End Sub

Private Sub cmdFindNext_Click()
Dim lastrow
Dim myfname As String
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
myfname = txtFname.Text
For currentrow = 2 To lastrow
If Cells(currentrow, 1).Text = myfname Then

txtFname.Text = Cells(currentrow, 1).Text
txtLname.Text = Cells(currentrow, 2)

End If
Next currentrow
txtFname.SetFocus
End Sub

Private Sub cmdFindPrevious_Click()
Dim lastrow
Dim myfname As String
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
myfname = txtFname.Text

For currentrow = lastrow To 2 Step -1
If Cells(currentrow, 1).Text = myfname Then

txtFname.Text = Cells(currentrow, 1).Text
txtLname.Text = Cells(currentrow, 2)

End If
Next currentrow
txtFname.SetFocus
End Sub

Private Sub cmdNext_Click()
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
currentrow = currentrow + 1
If currentrow = lastrow + 1 Then
MsgBox (“You have reached the last row of data!”)
currentrow = lastrow
End If
txtFname.Text = Cells(currentrow, 1).Text
txtLname.Text = Cells(currentrow, 2).Text

End Sub

Private Sub cmdPrevious_Click()
currentrow = currentrow – 1
If currentrow > 1 Then
txtFname.Text = Cells(currentrow, 1).Text
txtLname.Text = Cells(currentrow, 2).Text
ElseIf currentrow = 1 Then
MsgBox “Now you are in the header row!”
currentrow = currentrow + 1
End If
End Sub

Private Sub cmdQuit_Click()
Unload UserForm1
End Sub

Private Sub cmdUpdate_Click()
Dim fname As String, lname As String
fname = txtFname.Text
Cells(currentrow, 1).Value = fname
lname = txtLname.Text
Cells(currentrow, 2).Value = lname

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
currentrow = 2
txtFname.Text = Cells(currentrow, 1).Text
txtLname.Text = Cells(currentrow, 2).Text

End Sub

Private Sub cmdDelete_Click()
Dim lastrow
Dim myfname As String
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
myfname = txtFname.Text
For currentrow = 2 To lastrow
If Cells(currentrow, 1).Text = myfname Then

Cells(currentrow, 1).EntireRow.Delete

End If
Next currentrow
txtFname.SetFocus
End Sub

Of course, if you wish you can built safe-guards for buttons like the delete button by reconfirming using a message box from the user whether he would really like to delete the record. We have used two fields or columns of data but you can use as many columns as you like and you would have to take care of such changes in your VBA or macro code. As mentioned in the training video, you can also define the variable ‘lastrow’ as a ‘module wide’ variable which can be accessed by the VBA code of all the command buttons.

Download sample file by clicking on the Excel icon: