Update existing data using a UserForm

malani2 · Jul 13th 2016

Aloha,

I am very new to VBA and have created a UserForm for adding data to a worksheet by basically cutting, pasting and editing codes that I have found by searching the internet. Therefore, I know that my command button codes are not very consistent. I have gotten to the point that I can add new records to the sheet, find an existing record and populating the data back in the UserForm, however I am now stuck at updating an existing record that I just found.

Here's what I got. The cmdUpdate_Click() area is where I'm in trouble. Instead of updating the info that I just found with cmdFind_Click(), it adds a duplicate entry with the new information. (Note I deleted some code that is working fine for dropdown lists as I didn't think you needed all that info, but if it's necessary I can paste my full code. Also my actual report has over 30 columns, I reduced it down to 4 here)

Code

Dim currentrow As Long
Private Sub cmdFind_Click()
Dim lastrow
Dim myfind As String
lastrow = Sheets("Inventory List").Range("A" & Rows.Count).End(xlUp).Row
myfind = txtReportNumber.Text
For currentrow = 3 To lastrow
If Cells(currentrow, 1).Text = myfind Then
txtReportNumber.Value = Cells(currentrow, 1).Value
cmbCounty.Value = Cells(currentrow, 2).Value
cmbClass.Value = Cells(currentrow, 3).Value
txDateOff.Value = Cells(currentrow, 4).Value
End If
Next currentrow
txtReportNumber.SetFocus
End Sub
Private Sub cmdUpdate_Click()
Cells(currentrow, 1).Value = txtReportNumber.Value
Cells(currentrow, 2).Value = cmbCounty.Value
Cells(currentrow, 3).Value = cmbClass.Value
Cells(currentrow, 4).Value = txDateOff.Value
'clear the data
Me.txtReportNumber.Value = ""
Me.cmbCounty.Value = ""
Me.cmbClass.Value = ""
Me.txDateOff.Value = ""
Me.txtReportNumber.SetFocus
End Sub
Private Sub cmdSave_Click()
Dim ws As Worksheet
Set ws = Worksheets("Inventory List")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'copy the data to the database
With ws
.Cells(iRow, 1).Value = Me.txtReportNumber.Value
.Cells(iRow, 2).Value = Me.cmbCounty.Value
.Cells(iRow, 3).Value = Me.cmbClass.Value
.Cells(iRow, 4).Value = Me.txDateOff.Value
End With
'clear the data
Me.txtReportNumber.Value = ""
Me.cmbCounty.Value = ""
Me.cmbClass.Value = ""
Me.txDateOff.Value = ""
Me.txtReportNumber.SetFocus
End Sub

Display More

**Ger Plante** · Jul 13th 2016

Re: Update existing data using a UserForm

Hi Malani, - welcome to the forum

So in summary - my understanding is
cmdFind will populate the user form with values from the database, based on the search criteria "txtReportNumber.Text"
cmdUpdate will take the values from the user form and stick them back into the database, based on the "currentrow" variable

Your coding approach is not wrong, but it is not efficient. You use loops to loop through each row in the "FIND" procedure, which is OK, but the worst case scenario is you have check/test every single row to find the data you want to update. Using ".FIND" method on a range would be quicker as it would go directly to the correct record. However, lets try and fix what you have first before complicating matters a little.

Code

Dim currentrow As Long
Private Sub cmdFind_Click()
Dim lastrow
Dim myfind As String
lastrow = Sheets("Inventory List").Range("A" & Rows.Count).End(xlUp).Row
myfind = txtReportNumber.Text
For currentrow = 3 To lastrow
If Cells(currentrow, 1).Text = myfind Then
txtReportNumber.Value = Cells(currentrow, 1).Value
cmbCounty.Value = Cells(currentrow, 2).Value
cmbClass.Value = Cells(currentrow, 3).Value
txDateOff.Value = Cells(currentrow, 4).Value
End If
Next currentrow
txtReportNumber.SetFocus
End Sub

Display More

You have defined current row to be a global variable so its value is visible to both cmdFIND and cmdUpdate. This is fine. However, your problem I think lies in the fact that your loop goes from the first row to the last row, which increments currentrow by 1 each time. When the correct record is found (Cells(currentrow, 1).Text = myfind) and your user form is populated with data from the sheet, the loop continues... so Currentrow is now counting rows right down to the end of the list to the very last row (currentrow = 3 To lastrow). So while you have found the data and populated the user form, you have lost the "currentrow" position. You need to break or exit the loop when the data is found.

Code

For currentrow = 3 To lastrow
If Cells(currentrow, 1).Text = myfind Then
txtReportNumber.Value = Cells(currentrow, 1).Value
cmbCounty.Value = Cells(currentrow, 2).Value
cmbClass.Value = Cells(currentrow, 3).Value
txDateOff.Value = Cells(currentrow, 4).Value
exit for 'this ensures that currentrow stays at the correct row value.
End If
Next currentrow

Display More

If you want to do this without using loops, you would set a range variable (again a global variable) to find your string... something like this:

Code

dim r as range
Private Sub cmdFind_Click()
Dim myfind As String
myfind = txtReportNumber.Text
on error resume next
set r = activesheet.columns(1).find(myfind)
on error goto 0
if not r is nothing then
txtReportNumber.Value = r.Value
cmbCounty.Value = r.offset(,1).Value
cmbClass.Value = r.offset(,2).Value
txDateOff.Value = r.offset(,3).Value
else
msgbox "Not found"
end if
txtReportNumber.SetFocus
End Sub

Display More

the range variable R now points to the cell where txtReportNumber is found (or is "nothing" if not found), and this can then be used in the update procedure. I'll leave that code up to you

THE ABOVE CODE IS HAND TYPED AND ALL UNTESTED (I'll be shocked if it worked without errors )

HTH
Ger

malani2 · Jul 13th 2016

Re: Update existing data using a UserForm

Ger,

thank you so much. I like your find code much better. It's much faster with a lot of info. I'm going to start working on the update code, so I may be back if I can't figure it out.

**Ger Plante** · Jul 13th 2016

Re: Update existing data using a UserForm

Quote from malani2;773875

Ger,

thank you so much. I like your find code much better. It's much faster with a lot of info. I'm going to start working on the update code, so I may be back if I can't figure it out.

Not a problem.... Start a new thread if you have any questions and I or someone else will help out!

Ger

malani2 · Jul 13th 2016

Re: Update existing data using a UserForm

Ger,

I believe I'm already beyond my VBA skills. I have no idea what to do to get the info to update now that I've used your find option.

Code

Private Sub cmdFind_Click()
Dim r As Range
Dim myfind As String
myfind = txtReportNumber.Text
Set r = Worksheets("Inventory List").Columns(1).Find(myfind)
On Error GoTo 0
If Not r Is Nothing Then
txtReportNumber.Value = r.Value
cmbCounty.Value = r.Offset(, 1).Value
cmbClass.Value = r.Offset(, 2).Value
txDateOff.Value = r.Offset(, 3).Value
Else
MsgBox "Report Number Not Found"
End If
txtReportNumber.SetFocus
End Sub
Private Sub cmdUpdate_Click()
Cells(currentrow, 1).Value = txtReportNumber.Value
Cells(currentrow, 2).Value = cmbCounty.Value
Cells(currentrow, 3).Value = cmbClass.Value
Cells(currentrow, 4).Value = txDateOff.Value
Me.txtReportNumber.Value = ""
Me.cmbCounty.Value = ""
Me.cmbClass.Value = ""
Me.txDateOff.Value = ""
Me.txtReportNumber.SetFocus
End Sub

Display More

**Ger Plante** · Jul 14th 2016

Re: Update existing data using a UserForm

No worries,

Firstly keep R as a global variable, visible to all sub routines, do this by moving it to the very top, first line of your code, outside of all the sub routines.

Code

Dim r As Range
Private Sub cmdFind_Click()

Now after the the find operation, R is pointing to that first cell so you can use it during the update...

Code

Private Sub cmdUpdate_Click()
R.Value = txtReportNumber.Value
R.offset(,1).Value = cmbCounty.Value
R.offset(,2).Value = cmbClass.Value
R.offset(,3).Value = txDateOff.Value
'Etc.

Hth

Ger

malani2 · Jul 14th 2016

Re: Update existing data using a UserForm

You are awesome, that worked perfectly.

**Ger Plante** · Jul 14th 2016

Re: Update existing data using a UserForm

noobsaibot · Mar 20th 2019

Quote

Set r = Worksheets("Inventory List").Columns(1).Find(myfind)

The above offsets the columns, but if the data on my sheet starts at row 4, how do i offset the row too

Share