Log In
Register
Home
October 25, 2017
Offline
Hi,
I found the code below am using to add new vendors, how can I modify it that can use for updating existing vendors just to update some information to already existing vendor when new information received to update only.
here is the code:
Private Sub CommandButton4_Click()
Dim lastrow As Long
lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Sheet1").Cells(lastrow, 1).Value = Me.TextBox1.Value
Sheets("Sheet1").Cells(lastrow, 2).Value = Me.TextBox5.Value
Sheets("Sheet1").Cells(lastrow, 3).Value = Me.TextBox2.Value
Sheets("Sheet1").Cells(lastrow, 4).Value = Me.TextBox3.Value
Sheets("Sheet1").Cells(lastrow, 5).Value = Me.TextBox4.Value
MsgBox " Data sucessfully Add"
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
End Sub
Thank you,
October 5, 2010
Offline
Hi Jose,
Please supply the workbook that you want this to work with. It's very difficult to provide you an answer without that workbook's data and userform.
regards
Phil
October 25, 2017
Offline
Hi Philip,
atttached.
October 25, 2017
Offline
Hi Phillip,
I added sample of the file, any help?
Thank you,
Trusted Members
February 13, 2021
Offline
Hi Jose,
Here try this. Double click on a row in Disc_Code to bring up your userform and the update button will update your information and unload your form. I had issues at first but I think that is because your cancel button isn't unloading your form, it's hiding it. You want your code to be Unload Me. I made that change as well. Hope this helps! Let me know if you have any questions.
The codes I wrote are housed in the worksheet before double click event and the userform click event for the update button.
October 25, 2017
Offline
Thank you very much, worked perfectly.
One question if possible how to add "NextRecord" & "PrevRecord" in the form.
thanks again.
Trusted Members
February 13, 2021
Offline
I would use offset. Offset 1 row for next record and offset -1 row for previous record. Just copy the code to fill the form and instead of cells.value you want cells.offset().value. Inside the () type either the 1 or the -1 for the rowoffset.
October 25, 2017
Offline
Sorry, but I have no idea what you mean would you mind writing it do I need to create two command button for previous and next?
Thank you again
Trusted Members
February 13, 2021
Offline
Something like this.
ActiveCell.Offset(rowoffset:=-1).Select
UserForm1.TextBox1.Value = Sheet2.Cells(ActiveCell.Row, 1).Value
UserForm1.TextBox2.Value = Sheet2.Cells(ActiveCell.Row, 2).Value
UserForm1.TextBox3.Value = Sheet2.Cells(ActiveCell.Row, 3).Value
UserForm1.TextBox4.Value = Sheet2.Cells(ActiveCell.Row, 4).Value
UserForm1.TextBox5.Value = Sheet2.Cells(ActiveCell.Row, 5).Value
UserForm1.TextBox6.Value = Sheet2.Cells(ActiveCell.Row, 6).Value
UserForm1.TextBox7.Value = Sheet2.Cells(ActiveCell.Row, 7).Value
UserForm1.TextBox8.Value = Sheet2.Cells(ActiveCell.Row, 8).Value
For some reason I was thinking about your project this morning and thought of a couple issues with my advice. This is the thing I could think of off hand to correct the issues I thought about. There might be a better way to do it, too; there are usually several ways in Excel.
Yes, you would need a way on the userform to execute the macro. Hope this gets you going.
1 Guest(s)
