Add, Edit and Update Worksheet Records from UserForm

Hello there, I have created a simple form that when filled out adds a line to a register. The items added to the register require updates which means i need a form that I can use to update records already on the register. The records have an ID which is the DMD number, however some lines will have the same DMD number which means the form would need a list-box depending on the DMD number selected.

Add, Edit and Update Worksheet Records from UserForm

Add, Edit and Update Worksheet Records from UserForm

Please could someone help! I have looked all over the internet and i cannot for the life of me find an example that i can edit and make my own! I’m very new to this and in time I am sure I will learn more but for the time being some help would be great!

Contributor Asked on November 10, 2016 in VBA: User Forms.
Add Comment
3 Answer(s)
Best answer

Hi, Please send your file to info@analysistabs.com.

Thanks-PNRao!

Keymaster Answered on November 10, 2016.

Email sent! Thanks!

on November 10, 2016.

Check the updated file:

Here is the code which we have added. Now you can select the Cell (AKI DMD NO) in the Column 2 and click the Edit/Update Button.

Create a global variable in the code module, this helps to identify the button click(new or update)

 

Public blnRecEdit As Boolean

New Procedure for Edit/Update button Call

 

Sub vbaf1_sbEditUpdate()
blnRecEdit = True
Dmdform.Show
End Sub

Code for Form Initialize Event:

Private Sub UserForm_Initialize()
Me.Caption = "Add New"
If blnRecEdit = True Then
'check if valid AKI DMD NO is selected
iRow = ActiveCell.Row
If Selection.Rows.Count = 1 And iRow > 18 And ActiveCell.Column = 2 And Trim(ActiveCell) <> "" Then
With Worksheets("DMDS")
Me.txtAKI.Value = .Cells(iRow, 2).Value
Me.txtNSN.Value = .Cells(iRow, 3).Value
Me.txtDESC.Value = .Cells(iRow, 4).Value
Me.txtQTY.Value = .Cells(iRow, 5).Value
Me.txtDATE.Value = .Cells(iRow, 6).Value
Me.txtRDD.Value = .Cells(iRow, 7).Value
Me.txtPTY.Value = .Cells(iRow, 8).Value
Me.txtREG.Value = .Cells(iRow, 9).Value
Me.txtVEH.Value = .Cells(iRow, 10).Value
Me.txtCOMP.Value = .Cells(iRow, 11).Value
Me.txtDMD.Value = .Cells(iRow, 12).Value
Me.txtAWB.Value = .Cells(iRow, 13).Value
Me.txtFLT.Value = .Cells(iRow, 14).Value
Me.txtPRO.Value = .Cells(iRow, 15).Value
End With
cmdAdd.Caption = "Update"
Me.Caption = "Edit and Update"
Else
blnRecEdit = False
MsgBox "Please select valid DMD NO Range.", vbInformation, "Please check!"
End If
Else
cmdAdd.Caption = "Submit"
End If
End Sub

Change in the code for Submit button (cmdAdd_Click):

'Changed By PNRao | VBAF1
If blnRecEdit = True Then
iRow = ActiveCell.Row
Else
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
End If

 

Thanks!

 

on November 10, 2016.
Add Comment

Hi, You can use the same userform with another button, please follow the below guidelines:

Approach:  

  1. User can Select DMD number in the first column and Click ‘Update’ button
  2. Same Userform will be filled with the selected row data and shown to the user to make changes
  3. User will click the submit button to update the existing record.

Code:

  1. Maintain two different buttons for Adding and updating records
  2. When user clicks on Update button in worksheet:  Call the user form and fill the textboxes from the selected row while initiating the Userform
  3. When user clicks on Submit button in Userfrom:  Enter the textbox values into the selected row

Hope this helps! Thanks!!

Keymaster Answered on November 10, 2016.
Add Comment

Thank you for replying but I am completely useless at this! Would you be able to provide the code I could use?  I would be happier with an additional button on the form to Update and a listbox showing results for the required DMD number.

I understand if your really busy but any help would be greatly appreciated.

Contributor Answered on November 10, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.