UserForm VBA code to update data

brett1966

New Member
Joined
Apr 20, 2018
Messages
13
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:

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
 
Last edited by a moderator:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Your initialize event should always be
Code:
Private Sub UserForm_Initialize()
regardless of the userform name
 
Upvote 0
When you say "nothing happen", does the message box appear?
 
Upvote 0
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
 
Upvote 0
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?

Code:
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
 
Upvote 0
Try
Code:
currentrow = Range("B" & Rows.count).End(xlUp).Offset(1).row
If currentrow < 5 Then currentrow = 5
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top