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:

31 thoughts on “How to Update Excel Worksheet Records with UserForm

  1. Kevin

    Hello Dinesh. I have a column (A) of numbers from 1 to 45 down. (for the lottery). In the next column (B) I have the intervals of those numbers ( example No 6 skipped 3 games then was drawn)
    Number 12 skipped 5 games then was drawn, and so on. So all in all each Number has an interval. score or rating.
    How can I store (record) into the A column, the numbers that have been drawn (6 main numbers, and 2 supplementary numbers) making 8 drawn numbers, and make it so that the Interval column opposite , is automatically adjust or updated, to show the appropriate Interval accordingly..
    So, basically, I mean to enter the winning numbers into the appropriate column, then the Interval column adjusts to the drawn numbers. So if the winning number 6 was drawn, I would enter 6,, then the interval column would show a zero as it’s previous interval, (lets say 11) is then not showing as it has just been updated.
    Could you please help? and show a user form doing this?
    Thank you
    Kevin ..

    Reply
  2. Franck

    Really enjoy your videos: they solved many problems I had with a complicated database (am a rookie in Excel). OUTSTANDING work!

    My question here is simple: on previous video, we talked about saving the data from a user form unto multiple sheets; so using that user form I can “create” multiple entries on several sheets; BUT, what if I want to UPDATE data using the same user form (by adding an update button) , will this method be adequate? as no multiple sheets are defined here.
    Ergo, updating all the sheets at once via the user form “update” button

    Reply
  3. Ray

    Hello Dinesh, thank you so much for your videos. They are very helpful, especially for a beginner like me. I have used your exact coding however when I search the list of names I have in my list, I go and update the surname and click ADD and it enters it on a new line! I am only wanting to update to existing names, Can you help me so when I press ADD it can only update the change, Next and Previous records buttons work great and can be changed no problem.

    Thanks,

    Ray

    Reply
  4. Julia

    Hi Dinesh,
    if I have 1 more column is gender and i am going to have option button indicate whether the person is male or female. So, how should i code the option button in the Find Next and Find Previous when i searching the name of the person and the user form will show the gender of the person in the option button Thank you!

    Reply
  5. Anthony Austin

    Happy New Years Dinesh. Thanks for the excellent videos. I am need of assistance. I am a maintenance supervisor and I use excel to track and count scheduled services. The problem that I have and want to fix with vba is the copying and pasting I have to do. I have a masterwork book that has a sheet named for each company that I work with. On the sheet it has tables that span a year and calculate how many of each type of equipment is coming due service within a specific month. Basically, I want to make a button that copies sheet one of BMW and past it into my masterwork book BMWSVC sheet so that my formulas can count. Can you please assist?

    Reply
  6. Thein Aung

    Hello Dinesh, I am a beginner, please help me with my retail shop program. I want to auto fill price text box when I select an item in itemcombo box.there is 2 column list of pharmacy,it has name and price.Here is my code: thank you

    Private Sub cmdsend_Click()
    Dim emptyrow As Long
    Dim abc As Range
    Sheet3.Activate
    Sheet2.Activate
    Set abc = Sheet3.Range(“A1:B28”)
    emptyrow = WorksheetFunction.CountA(Range(“A:A”)) + 1
    Cells(emptyrow, 1).Value = Itemcombo.Value
    Txtprice.Value = WorksheetFunction.VLookup(Itemcombo.Value, abc, 2, False)
    Cells(emptyrow, 2).Value = Txtprice.Value
    Cells(emptyrow, 3).Value = Txtqty.Value
    Txtpaid.Value = Txtqty.Value * Txtprice.Value
    Cells(emptyrow, 4).Value = Txtpaid.Value
    End Sub

    Private Sub UserForm_Initialize()
    Itemcombo.Value = “”
    Txtqty.Value = “”
    Txtprice.Value = “”
    Txtpaid.Value = “”
    Itemcombo.SetFocus
    End Sub

    Reply
    1. John Withaar

      Thank you Dinesh, for sharing this solution on how to update records using a userform.
      This could be the long sought-after solution for me, except that it is not quite working in my model.
      I have created a worksheet with 74 columns and several user forms feeding into that sheet using your outline. One userform (UserForm1) is for entering new data and contains a number of ComboBoxes and DTPicker fields. This UserForm1 is working fine.
      Since I don’t know how to use a ComboBox as a lookup field, I’ve created a second userform (UserForm2), which is identical in layout and is comprised of only text boxes, where a user can enter a name in the fname field and find a match, or the next match, and edit the data. Everything seems to be working (like Clear form, Next or Previous record), except for the most critical ones, the FindNextMatch or FindPreviousMatch. Those two controls return no values.

      btw: my user forms are multipage forms.

      Here is my code for UserForm2 (I’ve only build it up to 34 columns, wanting to make sure it is working before I add the other 40 columns):

      Any insight you can provide will be much appreciated.

      John

      Code:

      Dim currentrow As Long

      Private Sub cmdAdd_Click()
      Dim lastrow As Long
      lastrow = Sheets(“DB”).Range(“A” & Rows.Count).End(xlUp).Row
      Sheets(“DB”).Cells(lastrow + 1, “A”).Value = txtfname.Text
      Sheets(“DB”).Cells(lastrow + 1, “B”).Value = txtlname.Text
      Sheets(“DB”).Cells(lastrow + 1, “C”).Value = txtdate.Text
      Sheets(“DB”).Cells(lastrow + 1, “D”).Value = txtnumber.Text
      Sheets(“DB”).Cells(lastrow + 1, “E”).Value = txtdescription.Text
      Sheets(“DB”).Cells(lastrow + 1, “F”).Value = txtpurpose.Text
      Sheets(“DB”).Cells(lastrow + 1, “G”).Value = txtmanager.Text
      Sheets(“DB”).Cells(lastrow + 1, “H”).Value = txtdepartment.Text
      Sheets(“DB”).Cells(lastrow + 1, “I”).Value = txtterm.Text
      Sheets(“DB”).Cells(lastrow + 1, “J”).Value = txtperiod.Text
      Sheets(“DB”).Cells(lastrow + 1, “K”).Value = txtstart.Text
      Sheets(“DB”).Cells(lastrow + 1, “L”).Value = txtend.Text
      Sheets(“DB”).Cells(lastrow + 1, “M”).Value = txtdeptnum.Text
      Sheets(“DB”).Cells(lastrow + 1, “N”).Value = txtlineitem.Text
      Sheets(“DB”).Cells(lastrow + 1, “O”).Value = txttypeofcontract.Text
      Sheets(“DB”).Cells(lastrow + 1, “P”).Value = txtdate.Text
      Sheets(“DB”).Cells(lastrow + 1, “Q”).Value = txtvalue.Text
      Sheets(“DB”).Cells(lastrow + 1, “R”).Value = txtbillcycle.Text
      Sheets(“DB”).Cells(lastrow + 1, “S”).Value = txtamountpaid.Text
      Sheets(“DB”).Cells(lastrow + 1, “T”).Value = txtdatepaid.Text
      Sheets(“DB”).Cells(lastrow + 1, “U”).Value = txtnextpay.Text
      Sheets(“DB”).Cells(lastrow + 1, “V”).Value = txtexceedhours.Text
      Sheets(“DB”).Cells(lastrow + 1, “W”).Value = txtexceedamount.Text
      Sheets(“DB”).Cells(lastrow + 1, “X”).Value = txtexceedperiod.Text
      Sheets(“DB”).Cells(lastrow + 1, “Y”).Value = txthourlybill.Text
      Sheets(“DB”).Cells(lastrow + 1, “Z”).Value = txthourlyservice.Text
      Sheets(“DB”).Cells(lastrow + 1, “AA”).Value = txtdailybill.Text
      Sheets(“DB”).Cells(lastrow + 1, “AB”).Value = txtdailyservice.Text
      Sheets(“DB”).Cells(lastrow + 1, “AC”).Value = txtweeklybill.Text
      Sheets(“DB”).Cells(lastrow + 1, “AD”).Value = txtweeklyservice.Text
      Sheets(“DB”).Cells(lastrow + 1, “AE”).Value = txtmonthlybill.Text
      Sheets(“DB”).Cells(lastrow + 1, “AF”).Value = txtmonthlyservice.Text
      Sheets(“DB”).Cells(lastrow + 1, “AG”).Value = txtannualbill.Text
      Sheets(“DB”).Cells(lastrow + 1, “AH”).Value = txtannualservice.Text

      End Sub

      Private Sub cmdClear_Click()
      txtfname.Text = “”
      txtlname.Text = “”
      txtdate.Text = “”
      txtnumber.Text = “”
      txtdescription.Text = “”
      txtpurpose.Text = “”
      txtmanager.Text = “”
      txtdepartment.Text = “”
      txtterm.Text = “”
      txtperiod.Text = “”
      txtstart.Text = “”
      txtend.Text = “”
      txtdeptnum.Text = “”
      txtlineitem.Text = “”
      txttypeofcontract.Text = “”
      txtdate.Text = “”
      txtvalue.Text = “”
      txtbillcycle.Text = “”
      txtamountpaid.Text = “”
      txtdatepaid.Text = “”
      txtnextpay.Text = “”
      txtexceedhours.Text = “”
      txtexceedamount.Text = “”
      txtexceedperiod.Text = “”
      txthourlybill.Text = “”
      txthourlyservice.Text = “”
      txtdailybill.Text = “”
      txtdailyservice.Text = “”
      txtweeklybill.Text = “”
      txtweeklyservice.Text = “”
      txtmonthlybill.Text = “”
      txtmonthlyservice.Text = “”
      txtannualbill.Text = “”
      txtannualservice.Text = “”
      End Sub
      Private Sub cmdFindNext_Click()

      Dim lastrow
      Dim myfname As String
      lastrow = Sheets(“DB”).Range(“A” & Rows.Count).End(xlUp).Row
      myfname = txtfname.Text
      For currentrow = 3 To lastrow
      If Cells(currentrow, 1).Text = myfname Then

      txtfname.Text = Sheets(“DB”).Cells(currentrow, 1).Text
      txtlname.Text = Sheets(“DB”).Cells(currentrow, 2)
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 3)
      txtnumber.Text = Sheets(“DB”).Cells(currentrow, 4)
      txtdescription.Text = Sheets(“DB”).Cells(currentrow, 5)
      txtpurpose.Text = Sheets(“DB”).Cells(currentrow, 6)
      txtmanager.Text = Sheets(“DB”).Cells(currentrow, 7)
      txtdepartment.Text = Sheets(“DB”).Cells(currentrow, 8)
      txtterm.Text = Sheets(“DB”).Cells(currentrow, 9)
      txtperiod.Text = Sheets(“DB”).Cells(currentrow, 10)
      txtstart.Text = Sheets(“DB”).Cells(currentrow, 11)
      txtend.Text = Sheets(“DB”).Cells(currentrow, 12)
      txtdeptnum.Text = Sheets(“DB”).Cells(currentrow, 13)
      txtlineitem.Text = Sheets(“DB”).Cells(currentrow, 14)
      txttypeofcontract.Text = Sheets(“DB”).Cells(currentrow, 15)
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 16)
      txtvalue.Text = Sheets(“DB”).Cells(currentrow, 17)
      txtbillcycle.Text = Sheets(“DB”).Cells(currentrow, 18)
      txtamountpaid.Text = Sheets(“DB”).Cells(currentrow, 19)
      txtdatepaid.Text = Sheets(“DB”).Cells(currentrow, 20)
      txtnextpay.Text = Sheets(“DB”).Cells(currentrow, 21)
      txtexceedhours.Text = Sheets(“DB”).Cells(currentrow, 22)
      txtexceedamount.Text = Sheets(“DB”).Cells(currentrow, 23)
      txtexceedperiod.Text = Sheets(“DB”).Cells(currentrow, 24)
      txthourlybill.Text = Sheets(“DB”).Cells(currentrow, 25)
      txthourlyservice.Text = Sheets(“DB”).Cells(currentrow, 26)
      txtdailybill.Text = Sheets(“DB”).Cells(currentrow, 27)
      txtdailyservice.Text = Sheets(“DB”).Cells(currentrow, 28)
      txtweeklybill.Text = Sheets(“DB”).Cells(currentrow, 29)
      txtweeklyservice.Text = Sheets(“DB”).Cells(currentrow, 30)
      txtmonthlybill.Text = Sheets(“DB”).Cells(currentrow, 31)
      txtmonthlyservice.Text = Sheets(“DB”).Cells(currentrow, 32)
      txtannualbill.Text = Sheets(“DB”).Cells(currentrow, 33)
      txtannualservice.Text = Sheets(“DB”).Cells(currentrow, 34)

      End If
      Next currentrow
      txtfname.SetFocus

      End Sub

      Private Sub cmdFindPrevious_Click()

      Dim lastrow
      Dim myfname As String
      lastrow = Sheets(“DB”).Range(“A” & Rows.Count).End(xlUp).Row
      myfname = txtfname.Text

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

      txtfname.Text = Sheets(“DB”).Cells(currentrow, 1).Text
      txtlname.Text = Sheets(“DB”).Cells(currentrow, 2)
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 3)
      txtnumber.Text = Sheets(“DB”).Cells(currentrow, 4)
      txtdescription.Text = Sheets(“DB”).Cells(currentrow, 5)
      txtpurpose.Text = Sheets(“DB”).Cells(currentrow, 6)
      txtmanager.Text = Sheets(“DB”).Cells(currentrow, 7)
      txtdepartment.Text = Sheets(“DB”).Cells(currentrow, 8)
      txtterm.Text = Sheets(“DB”).Cells(currentrow, 9)
      txtperiod.Text = Sheets(“DB”).Cells(currentrow, 10)
      txtstart.Text = Sheets(“DB”).Cells(currentrow, 11)
      txtend.Text = Sheets(“DB”).Cells(currentrow, 12)
      txtdeptnum.Text = Sheets(“DB”).Cells(currentrow, 13)
      txtlineitem.Text = Sheets(“DB”).Cells(currentrow, 14)
      txttypeofcontract.Text = Sheets(“DB”).Cells(currentrow, 15)
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 16)
      txtvalue.Text = Sheets(“DB”).Cells(currentrow, 17)
      txtbillcycle.Text = Sheets(“DB”).Cells(currentrow, 18)
      txtamountpaid.Text = Sheets(“DB”).Cells(currentrow, 19)
      txtdatepaid.Text = Sheets(“DB”).Cells(currentrow, 20)
      txtnextpay.Text = Sheets(“DB”).Cells(currentrow, 21)
      txtexceedhours.Text = Sheets(“DB”).Cells(currentrow, 22)
      txtexceedamount.Text = Sheets(“DB”).Cells(currentrow, 23)
      txtexceedperiod.Text = Sheets(“DB”).Cells(currentrow, 24)
      txthourlybill.Text = Sheets(“DB”).Cells(currentrow, 25)
      txthourlyservice.Text = Sheets(“DB”).Cells(currentrow, 26)
      txtdailybill.Text = Sheets(“DB”).Cells(currentrow, 27)
      txtdailyservice.Text = Sheets(“DB”).Cells(currentrow, 28)
      txtweeklybill.Text = Sheets(“DB”).Cells(currentrow, 29)
      txtweeklyservice.Text = Sheets(“DB”).Cells(currentrow, 30)
      txtmonthlybill.Text = Sheets(“DB”).Cells(currentrow, 31)
      txtmonthlyservice.Text = Sheets(“DB”).Cells(currentrow, 32)
      txtannualbill.Text = Sheets(“DB”).Cells(currentrow, 33)
      txtannualservice.Text = Sheets(“DB”).Cells(currentrow, 34)
      End If
      Next currentrow
      txtfname.SetFocus

      End Sub

      Private Sub cmdNext_Click()

      lastrow = Sheets(“DB”).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 = Sheets(“DB”).Cells(currentrow, 1).Text
      txtlname.Text = Sheets(“DB”).Cells(currentrow, 2).Text
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 3).Text
      txtnumber.Text = Sheets(“DB”).Cells(currentrow, 4).Text
      txtdescription.Text = Sheets(“DB”).Cells(currentrow, 5).Text
      txtpurpose.Text = Sheets(“DB”).Cells(currentrow, 6).Text
      txtmanager.Text = Sheets(“DB”).Cells(currentrow, 7).Text
      txtdepartment.Text = Sheets(“DB”).Cells(currentrow, 8).Text
      txtterm.Text = Sheets(“DB”).Cells(currentrow, 9).Text
      txtperiod.Text = Sheets(“DB”).Cells(currentrow, 10).Text
      txtstart.Text = Sheets(“DB”).Cells(currentrow, 11).Text
      txtend.Text = Sheets(“DB”).Cells(currentrow, 12).Text
      txtdeptnum.Text = Sheets(“DB”).Cells(currentrow, 13).Text
      txtlineitem.Text = Sheets(“DB”).Cells(currentrow, 14).Text
      txttypeofcontract.Text = Sheets(“DB”).Cells(currentrow, 15).Text
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 16).Text
      txtvalue.Text = Sheets(“DB”).Cells(currentrow, 17).Text
      txtbillcycle.Text = Sheets(“DB”).Cells(currentrow, 18).Text
      txtamountpaid.Text = Sheets(“DB”).Cells(currentrow, 19).Text
      txtdatepaid.Text = Sheets(“DB”).Cells(currentrow, 20).Text
      txtnextpay.Text = Sheets(“DB”).Cells(currentrow, 21).Text
      txtexceedhours.Text = Sheets(“DB”).Cells(currentrow, 22).Text
      txtexceedamount.Text = Sheets(“DB”).Cells(currentrow, 23).Text
      txtexceedperiod.Text = Sheets(“DB”).Cells(currentrow, 24).Text
      txthourlybill.Text = Sheets(“DB”).Cells(currentrow, 25).Text
      txthourlyservice.Text = Sheets(“DB”).Cells(currentrow, 26).Text
      txtdailybill.Text = Sheets(“DB”).Cells(currentrow, 27).Text
      txtdailyservice.Text = Sheets(“DB”).Cells(currentrow, 28).Text
      txtweeklybill.Text = Sheets(“DB”).Cells(currentrow, 29).Text
      txtweeklyservice.Text = Sheets(“DB”).Cells(currentrow, 30).Text
      txtmonthlybill.Text = Sheets(“DB”).Cells(currentrow, 31).Text
      txtmonthlyservice.Text = Sheets(“DB”).Cells(currentrow, 32).Text
      txtannualbill.Text = Sheets(“DB”).Cells(currentrow, 33).Text
      txtannualservice.Text = Sheets(“DB”).Cells(currentrow, 34).Text

      End Sub

      Private Sub cmdPrevious_Click()

      currentrow = currentrow – 1
      If currentrow > 2 Then
      txtfname.Text = Sheets(“DB”).Cells(currentrow, 1).Text
      txtlname.Text = Sheets(“DB”).Cells(currentrow, 2).Text
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 3).Text
      txtnumber.Text = Sheets(“DB”).Cells(currentrow, 4).Text
      txtdescription.Text = Sheets(“DB”).Cells(currentrow, 5).Text
      txtpurpose.Text = Sheets(“DB”).Cells(currentrow, 6).Text
      txtmanager.Text = Sheets(“DB”).Cells(currentrow, 7).Text
      txtdepartment.Text = Sheets(“DB”).Cells(currentrow, 8).Text
      txtterm.Text = Sheets(“DB”).Cells(currentrow, 9).Text
      txtperiod.Text = Sheets(“DB”).Cells(currentrow, 10).Text
      txtstart.Text = Sheets(“DB”).Cells(currentrow, 11).Text
      txtend.Text = Sheets(“DB”).Cells(currentrow, 12).Text
      txtdeptnum.Text = Sheets(“DB”).Cells(currentrow, 13).Text
      txtlineitem.Text = Sheets(“DB”).Cells(currentrow, 14).Text
      txttypeofcontract.Text = Sheets(“DB”).Cells(currentrow, 15).Text
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 16).Text
      txtvalue.Text = Sheets(“DB”).Cells(currentrow, 17).Text
      txtbillcycle.Text = Sheets(“DB”).Cells(currentrow, 18).Text
      txtamountpaid.Text = Sheets(“DB”).Cells(currentrow, 19).Text
      txtdatepaid.Text = Sheets(“DB”).Cells(currentrow, 20).Text
      txtnextpay.Text = Sheets(“DB”).Cells(currentrow, 21).Text
      txtexceedhours.Text = Sheets(“DB”).Cells(currentrow, 22).Text
      txtexceedamount.Text = Sheets(“DB”).Cells(currentrow, 23).Text
      txtexceedperiod.Text = Sheets(“DB”).Cells(currentrow, 24).Text
      txthourlybill.Text = Sheets(“DB”).Cells(currentrow, 25).Text
      txthourlyservice.Text = Sheets(“DB”).Cells(currentrow, 26).Text
      txtdailybill.Text = Sheets(“DB”).Cells(currentrow, 27).Text
      txtdailyservice.Text = Sheets(“DB”).Cells(currentrow, 28).Text
      txtweeklybill.Text = Sheets(“DB”).Cells(currentrow, 29).Text
      txtweeklyservice.Text = Sheets(“DB”).Cells(currentrow, 30).Text
      txtmonthlybill.Text = Sheets(“DB”).Cells(currentrow, 31).Text
      txtmonthlyservice.Text = Sheets(“DB”).Cells(currentrow, 32).Text
      txtannualbill.Text = Sheets(“DB”).Cells(currentrow, 33).Text
      txtannualservice.Text = Sheets(“DB”).Cells(currentrow, 34).Text

      ElseIf currentrow = 2 Then
      MsgBox (“Now you are in the header row!”)
      currentrow = currentrow + 1
      End If

      End Sub

      Private Sub cmdquit_Click()
      Unload UserForm2
      End Sub

      Private Sub cmdUpdate_Click()
      With Sheets(“DB”)
      Dim fname As String, lname As String, condate As String, connumber As String, description As String, purpose As String, manager As String, department As String, term As String, period As String, startdate As String, enddate As String, deptnum As String, lineitem As String, typeofcontract As String

      fname = txtfname.Text
      Sheets(“DB”).Cells(currentrow, 1).Value = fname
      lname = txtlname.Text
      Sheets(“DB”).Cells(currentrow, 2).Value = lname
      condate = txtdate.Text
      Sheets(“DB”).Cells(currentrow, 3).Value = condate
      connumber = txtnumber.Text
      Sheets(“DB”).Cells(currentrow, 4).Value = connumber
      description = txtdescription.Text
      Sheets(“DB”).Cells(currentrow, 5).Value = description
      purpose = txtpurpose.Text
      Sheets(“DB”).Cells(currentrow, 6).Value = purpose
      manager = txtmanager.Text
      Sheets(“DB”).Cells(currentrow, 7).Value = manager
      department = txtdepartment.Text
      Sheets(“DB”).Cells(currentrow, 8).Value = department
      term = txtterm.Text
      Sheets(“DB”).Cells(currentrow, 9).Value = term
      period = txtperiod.Text
      Sheets(“DB”).Cells(currentrow, 10).Value = period
      Start = txtstart.Text
      Sheets(“DB”).Cells(currentrow, 11).Value = Start
      enddate = txtend.Text
      Sheets(“DB”).Cells(currentrow, 12).Value = enddate
      deptnum = txtdeptnum.Text
      Sheets(“DB”).Cells(currentrow, 13).Value = deptnum
      lineitem = txtlineitem.Text
      Sheets(“DB”).Cells(currentrow, 14).Value = lineitem
      typeofcontract = txttypeofcontract.Text
      Sheets(“DB”).Cells(currentrow, 15).Value = typeofcontract
      dateentered = txtdate.Text
      Sheets(“DB”).Cells(currentrow, 16).Value = dateentered

      End With
      End Sub

      Private Sub MultiPage1_Change()

      End Sub

      Private Sub UserForm_Initialize()
      currentrow = 3
      txtfname.Text = Sheets(“DB”).Cells(currentrow, 1).Text
      txtlname.Text = Sheets(“DB”).Cells(currentrow, 2).Text
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 3).Text
      txtnumber.Text = Sheets(“DB”).Cells(currentrow, 4).Text
      txtdescription.Text = Sheets(“DB”).Cells(currentrow, 5).Text
      txtpurpose.Text = Sheets(“DB”).Cells(currentrow, 6).Text
      txtmanager.Text = Sheets(“DB”).Cells(currentrow, 7).Text
      txtdepartment.Text = Sheets(“DB”).Cells(currentrow, 8).Text
      txtterm.Text = Sheets(“DB”).Cells(currentrow, 9).Text
      txtperiod.Text = Sheets(“DB”).Cells(currentrow, 10).Text
      txtstart.Text = Sheets(“DB”).Cells(currentrow, 11).Text
      txtend.Text = Sheets(“DB”).Cells(currentrow, 12).Text
      txtdeptnum.Text = Sheets(“DB”).Cells(currentrow, 13).Text
      txtlineitem.Text = Sheets(“DB”).Cells(currentrow, 14).Text
      txttypeofcontract.Text = Sheets(“DB”).Cells(currentrow, 15).Text
      txtdate.Text = Sheets(“DB”).Cells(currentrow, 16).Text
      txtvalue.Text = Sheets(“DB”).Cells(currentrow, 17).Text
      txtbillcycle.Text = Sheets(“DB”).Cells(currentrow, 18).Text
      txtamountpaid.Text = Sheets(“DB”).Cells(currentrow, 19).Text
      txtdatepaid.Text = Sheets(“DB”).Cells(currentrow, 20).Text
      txtnextpay.Text = Sheets(“DB”).Cells(currentrow, 21).Text
      txtexceedhours.Text = Sheets(“DB”).Cells(currentrow, 22).Text
      txtexceedamount.Text = Sheets(“DB”).Cells(currentrow, 23).Text
      txtexceedperiod.Text = Sheets(“DB”).Cells(currentrow, 24).Text
      txthourlybill.Text = Sheets(“DB”).Cells(currentrow, 25).Text
      txthourlyservice.Text = Sheets(“DB”).Cells(currentrow, 26).Text
      txtdailybill.Text = Sheets(“DB”).Cells(currentrow, 27).Text
      txtdailyservice.Text = Sheets(“DB”).Cells(currentrow, 28).Text
      txtweeklybill.Text = Sheets(“DB”).Cells(currentrow, 29).Text
      txtweeklyservice.Text = Sheets(“DB”).Cells(currentrow, 30).Text
      txtmonthlybill.Text = Sheets(“DB”).Cells(currentrow, 31).Text
      txtmonthlyservice.Text = Sheets(“DB”).Cells(currentrow, 32).Text
      txtannualbill.Text = Sheets(“DB”).Cells(currentrow, 33).Text
      txtannualservice.Text = Sheets(“DB”).Cells(currentrow, 34).Text

      End Sub

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

      Cells(currentrow, 1).EntireRow.Delete

      End If
      Next currentrow
      txtfname.SetFocus
      End Sub

      Reply
  7. ted brightmore

    Hi Dinesh

    I have replicated your database updating example with mixed success. I am struggling with the update function. You demonstrate this by running it from next and previous records – this is works OK where similar records exist and the selected record is updated.

    My project has a unique identifier (fname as integer) I need to run the update following the find function. I will have 1000s of products so it isn’t practical to scan through to find the one you want and then update.

    My Process
    Enter unique identifier (fname) – FIND to display Lname (there will be about 50 others)
    Modify Lname then UPDATE – currently it always writes a new record at the lastrow or last row +1
    No matter what I try the record found does not update.

    Also the Find Next works OK for 2 similar records where it oscillates between the 2. It goes to the first and last and ignores any similar records in between, eg Record 1 = Smith. Record 10 = Jones…it doesn’t find find record 4 = Kelly. The find requires the next similar record. The code appears to suggest this but that’s not how it works.

    I am fairly new to VBA (having run only macros directly in excel and only fiddled with the code) but generally understand the logic. But my current project requires a userform to protect the database integrity

    Do hope you can help ?

    Many thanks
    Ted

    Reply
  8. Jan Swanepoel

    Where do I find sample file for “How to Update Excel Worksheet Records with UserForm”

    Thank you,
    Jan

    Reply
  9. Marcel Dings

    Dear All,

    First of all thanks for all the help by your video’s and codes.
    I was able to build a complete UserForm for searching, adding, updating together with the advanced filter tool.
    When starting Excel the UserForm is loaded automaticly and is working great! BUT…..

    Now i created a PowerPoint presentation (F5 full screen) in which i start this .xlsm by a hyperlink and what happens…..
    At the background the UserForm is started but the PowerPoint presentations keeps in front.
    When i switch to the UserForm (ALT-TAB) and i ends it, PowerPoint gives a message if i would like to open the .xlsm file!?!?
    When i say ok, the UserForm starts again but then in front of the PowerPoint presentation and everything is working.

    Why doesn’t it starts in front the first time?
    I also build in an application delay into the UserForm to see if it would work than.
    But it didn’t make any difference.

    Could anyone please help me?

    Regards from the Netherlands,
    Marcel Dings

    Reply
  10. Jeremy

    For some reason my Update button with the code provided keeps on adding the information on a new row. Any suggestions?

    Reply
  11. stefendeWesa Nusanumba

    I am Steven, I live i Indonesia. I have problem with coding Update data. Here is the coding :
    Private Sub CMDUpdate_Click()

    Dim Nis As String, Nama As String, TempatLahir As String, TglLahir As String, Kelamin As String, _
    Alamat As String, Agama As String, NISN As String, HP As String, SKHUN As String, NamaIbu As String, _
    ThnLahirIbu As String, KerjaIbu As String, PendidikanIbu As String, NamaAyah As String, ThnAyah As String, _
    KerjaAyah As String, PendidikanAyah As String, PengAyah As String, NamaWali As String, TahunWali As String, _
    AlamatWali As String, KerjaWali As String, PengWali As String, KPS As String, NoKPS As String, StatusTinggal As String, _
    AlatTransportasi As String, Rombel As String, Box1 As String

    Nis = TXTNis.Text
    Cells(currentrow, 1).Value = Nis
    Nama = TXTNama.Text
    Cells(currentrow, 2).Value = Nama
    TempatLahir = TXTTempatLahir.Text
    Cells(currentrow, 3).Value = TempatLahir
    TglLahir = TXTTglLahir.Text
    Cells(currentrow, 4).Value = TglLahir
    Kelamin = CBOKelamin.Text
    Cells(currentrow, 5).Value = Kelamin
    Alamat = TXTAlamat.Text
    Cells(currentrow, 6).Value = Alamat
    Agama = CBOAgama.Text
    Cells(currentrow, 7).Value = Agama
    NISN = TXTNISN.Text
    Cells(currentrow, 8).Value = NISN
    HP = TXTHP.Text
    Cells(currentrow, 9).Value = HP
    SKHUN = TXTSKHUN.Text
    Cells(currentrow, 10).Value = SKHUN
    NamaIbu = TXTNamaIbu.Text
    Cells(currentrow, 11).Value = NamaIbu
    ThnLahirIbu = TXTThnLahirIbu.Text
    Cells(currentrow, 12).Value = ThnLahirIbu
    KerjaIbu = CBOKerjaIbu.Text
    Cells(currentrow, 13).Value = KerjaIbu
    PendidikanIbu = CBOPendidikanIbu.Text
    Cells(currentrow, 14).Value = PendidikanIbu
    NamaAyah = TXTNamaAyah.Text
    Cells(currentrow, 15).Value = NamaAyah
    ThnAyah = TXTThnAyah.Text
    Cells(currentrow, 16).Value = ThnAyah
    KerjaAyah = CBOKerjaAyah.Text
    Cells(currentrow, 17).Value = KerjaAyah
    PendidikanAyah = CBOPendidikanAyah.Text
    Cells(currentrow, 18).Value = PendidikanAyah
    PengAyah = TXTPengAyah.Text
    Cells(currentrow, 19).Value = PengAyah
    NamaWali = TXTNamaWali.Text
    Cells(currentrow, 20).Value = NamaWali
    TahunWali = TXTTahunWali.Text
    Cells(currentrow, 21).Value = TahunWali
    AlamatWali = TXTAlamatWali.Text
    Cells(currentrow, 22).Value = AlamatWali
    KerjaWali = CBOKerjaWali.Text
    Cells(currentrow, 23).Value = KerjaWali
    PengWali = TXTPengWali.Text
    Cells(currentrow, 24).Value = PengWali
    KPS = CBOKPS.Text
    Cells(currentrow, 25).Value = KPS
    NoKPS = TXTNoKPS.Text
    Cells(currentrow, 26).Value = NoKPS
    StatusTinggal = CBOStatusTinggal.Text
    Cells(currentrow, 27).Value = StatusTinggal
    AlatTransportasi = CBOAlatTransportasi.Text
    Cells(currentrow, 28).Value = AlatTransportasi
    Rombel = CBORombel.Text
    Cells(currentrow, 29).Value = Rombel
    Box1 = TextBox1.Text
    Cells(currentrow, 30).Value = Rombel

    End Sub

    I am very sorry cause my English too poor.
    I hope I will have the solution.
    Thank You so much

    Reply
  12. Krishna

    Hi Dinesh, I find your videos very helpful. I have a query, hope you will be able to help me with it.
    I have 2 sheets
    Sheet 1 contains a table with 3 columns:
    Column 1 : ID
    Column 2: location
    Column 3: Status
    Example data
    ID. Location. Status
    1. 1A. Quarantined
    1. 1B. Quarantined
    2. 1A. Quarantined

    On sheet 2 I have 2 drop downs for ID and Location. The location dropdown is dependent on the id. Once the user selects both the drop downs in the cell below they can choose option “released” and I have a button using which I want to update the status of the selected ( using drop downs)from quarantined to released.

    Can you please let me know how I can do it. I am not an expert in ba but self taught using YouTube and Google..

    Any help is much appreciated.

    Thanks
    Krishna

    Reply
  13. Rebecca Del Piano

    I have worked through every button. but can’t get the cmdPrevious_Click() to function. I have it written as:

    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

    I get a Compile Error: Syntax Error

    Reply
  14. Rex

    For some reason when I press the find next button and then edit. All it does is make a new line.

    Reply
  15. Hope

    The VBA code for cmdFindNext_Click is not working for me. I am a beginner and was wondering if you would be so kind and help me. Below is what I have in my user form. I am using Excel 2010.

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

    Lname.Text = Cells(currentrow, 1).Text
    Fname.Text = Cells(currentrow, 2)

    End If
    Next currentrow
    End Sub

    Thank you for your help and the videos.

    Reply
  16. Mystere White

    Dinesh,

    I too am having the same problem as Ray – When I update the data it pastes a new row — how do I do this without adding in delete previous row coding.

    Reply
  17. Dan

    What a brilliant video, thank you very much. Please how do i use this on a multiple worksheets. I have a combo box on the user form that has the worksheet names. How do i use this with data on the worksheet selected from the combo box.
    Thanks.

    Reply
  18. Pingback: Listbox RemoveItem question

  19. Bruce Lloyd

    Thank you for your interesting tutorials.

    I have a question about this. I have userform data entry similar to this but with often 100s of entries each added after the last line. Is it possible to keep the last entry visible at all times? Ie when the entry goes below last line visble on the screen I would like it to automatically scroll so it always remains visible for checking after each row of data is entered.

    There is an anxiliary question with this : the button which calls the userform is at the top of the table so if one scrolls to the last entry it is not visible so one has to scroll to the top to start data entry. So

    1) Can the file be saved so the top of the spreadsheet is always visible when file is opened? Then when the form initialises the last line becomes visible as per second para?
    2) or Can button be made to ‘float’ so it is always visible.

    Reply
    1. Reda Gonbasha

      Thanks my wonderful teacher for these wonderful videos, and the exact explanation.
      frankly I have benefited a lot from your presence.

      Reply
  20. Reda Gonbasha

    How do I search the User Form for the name and retrieve the rest of the data from the sheet. And printing them by pressing a specific button.
    Note that the file contains more than sheet.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *