Transfer Data from Userform

[Robby19]

I have a Userform that transfers data, sort of. I need to change the code, as it doesn't do what I need it to do. What ends up happening is that it creates a new row at the end of the table (good!). What it doesn't do, is add the userform data to that new row, but instead overwrites the data in row 5 (bad!). My table starts on A3 (headers), data starts on A4

I also need code to create a unique transaction #. As simple as 1, 2, 3, etc... that is automatically added when the user clicks the Save button. The transaction # should NOT be reset if a user deletes a row. For example, table shows Transaciton # 1, 2, 3. User deletes transaction 2 and then adds a new line of data using the form. This new line of data should be transaction 4. Transaction 2 is just gone forever.

The transaction # should be in Row A.

Below is my current code;

 ' Transfer Userform Data
 With Worksheets("Bookkeeping").Range("A4")
    .ListObject.ListRows.Add
    .Offset(1, 1).Value = ExpIncDrop.Value
    If ExpIncDrop.Value = "Expense" Then
     .Offset(1, 2).Value = TypeDrop.Value
    Else
     .Offset(1, 3).Value = TypeDrop.Value
    End If
    .Offset(1, 4).Value = DateTxt.Value
    .Offset(1, 5).Value = AmountTxt.Value
    .Offset(1, 6).Value = RemarksTxt.Value
 End With

 

[Robby19]

Figured out the problem.

rng.Cells(lrow, 4).Value = TypeDrop.Value

Should be

rng.Cells(1, 4).Value = TypeDrop.Value

 

[Robby19]

Final Save Button code;

Private Sub SaveCloseBtn_Click()
'Modified  9/21/2018

If Not IsDate(Me.DateTxt.Text) Then
    MsgBox "The Data entered is not a Date" & Chr(10) & "Please re-enter in the following format, m/dd/yyyy"
    Exit Sub
End If

' Check to see if Reason for Editing is blank
If Trim(ReasEditTxt.Text) <> "" Then

Dim fCell As Range
Dim idx As Long

idx = TransTxt

If idx = -1 Then Exit Sub

Set rng = Sheets("Bookkeeping").ListObjects("Bookkeeping").ListRows(idx + 1).Range
    
    ' Transfer Userform Data to Table Bookkeeping
    rng.Cells(1, 2).Value = Me.ExpIncDrop.Value
    If ExpIncDrop.Value = "Expense" Then
        rng.Cells(1, 3).Value = TypeDrop.Value
    Else
        rng.Cells(1, 4).Value = TypeDrop.Value
    End If
    rng.Cells(1, 5).Value = Me.DateTxt.Value
    rng.Cells(1, 6).Value = Me.AmountTxt.Value
    rng.Cells(1, 7).Value = Me.RemarksTxt.Value
    rng.Cells(1, 8).Value = Me.ReasEditTxt.Value
    rng.Cells(1, 9).Value = Now()
    
Else
MsgBox "Reason for Editing cannot be blank."
Exit Sub
End If

' Worksheet Protection
ThisWorkbook.ActiveSheet.Protect Password:="", AllowFiltering:=True

Unload Me

End Sub

 

[sericom]

That method will probably fail when transactions are deleted, you'd be better off searching for the row you need

Set rng = Sheets("Bookkeeping").ListObjects("Bookkeeping").ListColumns(1).Range.Find(TransTxt, LookIn:=xlValues, lookat:=xlWhole)

and then offset from there

rng.offset(,1).Value = Me.ExpIncDrop.Value

 

[Robby19]

What if transactions are never deleted?

For example, instead of deleting Transaction #2 , I could use the Edit Userform, clear the information and save it. That way Transaction 2 is still there, and the only cells that have data are the Reason for Editing (text = "Deleted") and Edited Date

 

[sericom]

Should be fine then