Help with Updating an Existing Record Using Userform

[mova]

I am VERY new to VBA and have learned how to build a userform and have built a basic one using guidelines I have found online. The userform captures basic data through text boxes, radio buttons and check boxes and exports it to the next free line on a worksheet in the workbook. It works perfectly.

Now what I want to do is be able to edit a record that was entered through the userform without going to the data on the output worksheet, but rather using a userform. Do I need to build a new userform for editing? Is there code I can add to my existing userform to accomplish this? I want to be able to pull up all the data based on the ID text field, make any changes and export it to the same row it came from.

Any help would be greatly appreciated - and remember, I am a new user!



My code:

Private Sub AddButton_Click()

Dim emptyRow As Long

'Make Sheet 1 (Data) Active
Sheets(2).Activate

'Determine Empty Row
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to Worksheet
Cells(emptyRow, 1).Value = IDtxt.Value
Cells(emptyRow, 2).Value = Nametxt.Value
Cells(emptyRow, 3).Value = Desctxt.Value

If OnBoardingBox.Value = True Then Cells(emptyRow, 4).Value = "On-Boarding"
If SalesDeepeningBox.Value = True Then Cells(emptyRow, 5).Value = "Sales Deepening"
If ServiceBox.Value = True Then Cells(emptyRow, 6).Value = "Service"
If RetentionBox.Value = True Then Cells(emptyRow, 7).Value = "Retention"
If RiskBox.Value = True Then Cells(emptyRow, 8).Value = "Risk"

If DirectMailBox.Value = True Then Cells(emptyRow, 9).Value = "Direct Mail"
If EmailBox.Value = True Then Cells(emptyRow, 10).Value = "Email"
If ChatBox.Value = True Then Cells(emptyRow, 11).Value = "Chat"
If MobileBox.Value = True Then Cells(emptyRow, 12).Value = "Mobile"

Cells(emptyRow, 13).Value = Fromtxt.Value
Cells(emptyRow, 14).Value = Totxt.Value

If NearRealButton.Value = True Then
Cells(emptyRow, 15).Value = "Near Real-Time"
Else
If DailyButton.Value = True Then
Cells(emptyRow, 15).Value = "Daily"
Else
If WeeklyButton.Value = True Then
Cells(emptyRow, 15).Value = "Weekly"
Else
If MonthButton.Value = True Then
Cells(emptyRow, 15).Value = "Month End"
Else
If QuarterButton.Value = True Then
Cells(emptyRow, 15).Value = "Quarter End"
Else
If YearButton.Value = True Then
Cells(emptyRow, 15).Value = "Year End"
End If
End If
End If
End If
End If
End If

Cells(emptyRow, 16).Value = NotesBox.Value

'check for a Trigger ID
If Trim(Me.IDtxt.Value) = "" Then
Me.IDtxt.SetFocus
MsgBox "Please enter an ID"
Exit Sub
End If

'clear the data
Me.IDtxt.Value = ""
Me.Nametxt.Value = ""
Me.Desctxt.Value = ""
Me.OnBoardingBox.Value = False
Me.SalesDeepeningBox.Value = False
Me.ServiceBox.Value = False
Me.RetentionBox.Value = False
Me.RiskBox.Value = False
Me.DirectMailBox.Value = False
Me.EmailBox.Value = False
Me.ChatBox.Value = False
Me.MobileBox.Value = False
Me.Fromtxt.Value = ""
Me.Totxt.Value = ""
Me.NearRealButton.Value = False
Me.DailyButton.Value = False
Me.WeeklyButton.Value = False
Me.MonthButton.Value = False
Me.QuarterButton.Value = False
Me.YearButton.Value = False
Me.NotesBox.Value = ""
Me.IDtxt.SetFocus

MsgBox "You have successfully submitted the data"

End Sub

Private Sub CancelButton_Click()

Unload Me

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please click the Cancel /Clear Button to Close the Form WITHOUT submitting the data"
End If

End Sub

 

[lenze]

For someone new to VBA, you seem to have a good grasp of the basics for UaerForms. Yes, you can use the same UserForm. It will be a good learning tool for you. I have not done this, but I envision something like this
Add 2 buttons to your form. One for New record, One for Edit. New record will run your existing macro. The Edit button, however, would call an Input box to enter your ID (Column "A"?). You would then use the Find method to locate the ID and determine the Row number of the record. This number would be used instead of emptyRow in your original code. Sorry I can't be more percise, but maybe this will get you started.

Good luck
lenze

 

[mova]

Thanks Lenze, but I think you are overestimating my VBA abilities. I don't know how to write code using Find, any advice there? I get what it would be in theory, but have no idea how to actually do it.

 

[lenze]

Here is some sample code which you can incorporate into yours

Sub Test()
Dim NRow As Long
Dim C As Range
Set C = Range("A2:A" & NRow).Find(InputBox("EnterID"), LookIn:=xlValues)
If Not C Is Nothing Then NRow = C.Row
MsgBox NRow
'Now use NRow in place of your emptyRow or set empty Row =NRow
End Sub

lenze

 

[mova]

Thanks Lenze. Do I need to add that to the bottom of my new code or assign it to a button to edit an existing record?

 

[mova]

Still having a ton of trouble, any other guidance you can provide?

 

[lenze]

Have you considered just using the Built-in DataForm (or better, John Walkenbach's enhanced Dataform)? Both have the capability to add new records and edit existing ones. What you are doing can certainly be done with a UserForm, but you may be re-inventing the wheel. John's form is available at his website (http://spreadsheetpage.com/index.php/dataform/home ) and it's FREE

lenze

 

[mova]

I have tried those, but they really don't have all the functionality I need. The one above is really a subset of what I really need to do, but if I can figure that out, I can do the rest.