View Full Version : Facing problem with update the record

03-10-2017, 06:46 AM
I really appreciate if anyone can help me to solve my problem.

I created userform in excel for the user to update the process and status (have process 1 & status, process 2 & status & etc) by using the combo box.

Other function are working well, except to save the data that user select from combo box into that particular record. I don't know what code should i use to assign overwrite data in the row that user searched the record.

I also attach the file and sample data inside the excel file18591 for you guys to check whether any part of it went wrong. I have been researched on Internet for these few days but I can't get any sample coding that similar to my situation.

03-10-2017, 07:16 AM
I marked some suggestions and changes in part of the macro to see if it helps

'Option Explicit ' <<<<<<<<<<<<<<<<<< I'd suggest using this

Dim row_number As Long ' <<<<<<<<<<<<<<<<<<scoped to module and used to be currentrow

Private Sub CmdSave_Click()
Sheets("Sheet1").Range("C" & row_number) = Me.Process1.Value '<<<<<<<<<<<<<<<< you weren't putting values back to worksheet
Sheets("Sheet1").Range("E" & row_number) = Me.Process2.Value
Sheets("Sheet1").Range("G" & row_number) = Me.Process3.Value
Sheets("Sheet1").Range("I" & row_number) = Me.Process4.Value
Sheets("Sheet1").Range("D" & row_number) = Me.Status1.Value
Sheets("Sheet1").Range("F" & row_number) = Me.Status2.Value
Sheets("Sheet1").Range("H" & row_number) = Me.Status3.Value
Sheets("Sheet1").Range("J" & row_number) = Me.Status4.Value
End Sub

Private Sub CmdSearch_Click()
If IWONoDisp.Text = "" Then
MsgBox "Please enter IWO No"
End If
row_number = 0

row_number = row_number + 1
item_in_review = Sheets("Sheet1").Range("A" & row_number)
If item_in_review = IWONoDisp.Text Then
Me.Process1.Value = Sheets("Sheet1").Range("C" & row_number)
Me.Process2.Value = Sheets("Sheet1").Range("E" & row_number)
Me.Process3.Value = Sheets("Sheet1").Range("G" & row_number)
Me.Process4.Value = Sheets("Sheet1").Range("I" & row_number)
Me.Status1.Value = Sheets("Sheet1").Range("D" & row_number)
Me.Status2.Value = Sheets("Sheet1").Range("F" & row_number)
Me.Status3.Value = Sheets("Sheet1").Range("H" & row_number)
Me.Status4.Value = Sheets("Sheet1").Range("J" & row_number)

Exit Do ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< stop when you find and leave row_number set

End If

Loop Until item_in_review = ""
End Sub

03-10-2017, 07:44 AM
Function IWORow() As Long
IWORow = Sheets("Sheet1").Range("A:A").Find(Me.IWONoDisp).Row
End Function
In other subs

row_number = IWORow

03-10-2017, 10:55 PM
Thank you you all for the effort to help solve the issue, it works well with Paul's code. I learned VB before during college time but i am not so interested in programming so never use it practically, only now i have to workout this userform in excel in order to facilitate the data entry for my colleague.