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)
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