How to update records in an Excel database with a user-form. When we create a database and make data entries, we are bound to make mistakes. Once we discover the errors or somebody points out to them, we need to take corrective action and update the records.
Watch the video below to see how we can update records in Excel with a user-form:
This video is also available on YouTube.
Here’s the VBA code:
Dim currentrow As Long ‘declaration of the global variable right at the top
Private Sub UserForm_Initialize()
currentrow = 2
TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)
End Sub
Private Sub cmdUpdate_Click()
answer = MsgBox(“Are you sure you want to update the record?”, vbYesNo + vbQuestion, “Update Record”)
If answer = vbYes Then
Cells(currentrow, 1) = TextBox1.Text
Cells(currentrow, 2) = TextBox2.Text
Cells(currentrow, 3) = TextBox3.Value
End If
End Sub
Further reading:
I have followed the instruction in the video. When I click on Update, the record being updated remains in its original row with none of the data having been changed. The updated record overwrites the record in the first row. The record that is originally in the first row is lost
Even am facing the same issue its actually updating the row which decleard in intiliz form code currentrow =2. Thats what is updating each time when i try to update any row in the sheet
I have followed the instruction in the video. When I click on Update, the record being updated remains in its original row with none of the data having been changed. The updated record overwrites the record in the first row. The record that is originally in the first row is lost
Hi anyone have the same issue? It keep updating the first row instead of the chosen row
Hi its Andrew
I really need some help
I have done all of the vba information and it works really well for the database and updating records
but I need to use the combobox for names not id numbers I don’t know how to change it
I am really hoping you can help me
Thanks Andrew
this code works try if you want to.
Private Sub CommandButton6_Click()
answer = MsgBox(“Are you sure you want to update the record?”, vbYesNo + vbQuestion, “Update Record”)
If answer = vbYes Then
Dim Product_ID As String
Product_ID = Trim(TextBox1.Text)
End If
lastrow = Worksheets(“Products”).Cells(Rows.count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets(“Products”).Cells(i, 1).Value = Product_ID Then
Worksheets(“Products”).Cells(i, 2).Value = TextBox2.Text
Worksheets(“Products”).Cells(i, 3).Value = TextBox3.Text
Worksheets(“Products”).Cells(i, 4).Value = TextBox4.Text
Worksheets(“Products”).Cells(i, 5).Value = TextBox5.Text
Worksheets(“Products”).Cells(i, 6).Value = TextBox6.Text
Worksheets(“Products”).Cells(i, 7).Value = TextBox7.Text
End If
Next
End Sub