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