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]

Your initialize event should always be

Private Sub UserForm_Initialize()

regardless of the userform name

 

[brett1966]

thank you

 

[Fluff]

You're welcome

 

[brett1966]

I made that change. Now when I run the submit command nothing happen!

 

[Fluff]

When you say "nothing happen", does the message box appear?

 

[brett1966]

Sorry, that was not a very clear answer.... yes the message box appears and I say yes to update the records. But no data is updated in the row

 

[brett1966]

A further development. The code is working. it's just not updating row 22 as this is my test row. It is updating row 5 as that is the row on UserForm_Initialize code. I set that because that is the first row of my data. Everything above that is headers etc. Do you have any thoughts as to what it should be?

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


End Sub

 

[Fluff]

Try

currentrow = Range("B" & Rows.count).End(xlUp).Offset(1).row
If currentrow < 5 Then currentrow = 5

 

[brett1966]

Ok, it seems we are getting closer. I have made those changes and it is updating the data after the last row. So it I select row 10 to edit data then it puts the data in Row 31