UserForm VBA code to update data

[brett1966]

Hi All, I have a UserForm which I use a combo box to select a row of data. I want to be able to add/edit the data in that row from the UserForm. However, I keep getting a Run- time error '1004' when I run the cmdSubmitData code to update 2 cells.

Any assistance would be great. Cheers

Here is my code:

Dim Currentrow As Long


Private Sub cmdSubmitData_click()
answer = MsgBox("Are you sure you want to update the records?", vbYesNo + vbQuestion, "update Record")
If answer = vbYes Then
Cells(Currentrow, 12) = txtNetWeight.Value
Cells(Currentrow, 13) = txtRate.Value
End If
End Sub


Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub cmbClear_Click()
txtPatch.Text = ""
txtBins.Text = ""
txtPatch.Text = ""
End Sub


Private Sub cmbTruckDockets_DropButt*******()
Dim i As Long, LastRow As Long
LastRow = Sheets("Sheet1").range("B" & Rows.Count).End(xlUp).Row
If Me.cmbTruckDockets.ListCount = 0 Then
For i = 2 To LastRow
Me.cmbTruckDockets.AddItem Sheets("Sheet1").Cells(i, "B").Value
Next i
End If


End Sub


Private Sub cmbTruckDockets_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("Sheet1").range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "B").Value = (Me.cmbTruckDockets) Or _
Sheets("Sheet1").Cells(i, "B").Value = Val(Me.cmbTruckDockets) Then
Me.txtBins = Sheets("Sheet1").Cells(i, "G").Value
Me.txtPatch = Sheets("Sheet1").Cells(i, "c").Value
End If
Next
End Sub


Private Sub UserForm2_Initialize()
Currentrow = 5
txtPatch = Cells(Currentrow, 3)
txtBins = Cells(Currentrow, 7)
txtNetWeight = Cells(Currentrow, 12)
txtRate = Cells(Currentrow, 13)




End Sub

 

[Fluff]

Can you please explain what you are trying to do?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...1235272-userform-vba-code-to-update-data.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[brett1966]

No Problems with Rule 13. We'll concentrate our efforts here. Thank you.

 

[brett1966]

I have 2 forms. The 1st form inputs 4 pieces of data into the worksheet when we harvest our fruit and each line of data is given a unique number that is traceable with us and our suppliers. When we the fruit is processed we receive more information regarding its weight, grades and costs etc which is where the second form comes in. There is up to 20 further pieces of data that needs to be entered into the worksheet that is related to each unique number.
Currently in testing, I am only concentrating on entering 2 extra pieces of data to get it in the correct row and column, the other 18 pieces of data will follow easily.

I have 30 lines of data in my worksheet for testing. When I use the 2nd form, I am wanting to enter the net weight (txtnetweight) and the bin rate(txtRate). Currently, when I select any record with the combo box it puts 2 extra pieces of information in the form in txtPatch, txtBins. This is so we can verify that we are definitely in the correct record before we enter all the data. As things are now, when I enter the data in txtNetweight & txtRate and submit it puts the data in the correct columns and puts in below the last line of data.

Thanks Fluff, I hope that makes sense.

 

[Fluff]

OK, which textbox contains the unique number & what column can that be found in?

 

[brett1966]

The unique number is found in column B and starts at row 5. On the form, it is only shown in the combo box as that is my "search" method.

 

[Fluff]

OK, try

Private Sub cmdSubmitData_click()
   Dim Fnd As Range
   If MsgBox("Are you sure you want to update the records?", vbYesNo + vbQuestion, "update Record") = vbYes Then
      Set Fnd = Range("B:B").Find(Me.[COLOR=#ff0000]ComboBox1[/COLOR].Value, , , xlWhole, , , False, , False)
      Cells(Fnd.row, 12) = txtNetWeight.Value
      Cells(Fnd.row, 13) = txtRate.Value
   End If
End Sub

Change the part in red to suit

 

[brett1966]

Fluff, you are a genius. It works perfectly. Thank you.

 

[Fluff]

Glad to help & thanks for the feedback