Add, Edit and Update Worksheet Records from UserForm in Excel VBA

Add Edit and Update Worksheet Records from User Form

Question: 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.

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!
Answer:

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

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

Approach:

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

Code:

Maintain two different buttons for Adding and updating records
When user clicks on Update button in worksheet: Call the user form and fill the text-boxes from the selected row while initiating the User-form
When user clicks on Submit button in User-from: Enter the text-box values into the selected row

Hope this helps! Thanks!!