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

 

[sericom]

Try this, change "Table1" if your table is named different

    With Worksheets("Bookkeeping").ListObjects("Table1")
        lrow = .ListColumns(1).Range.Rows.Count
        .DataBodyRange.Cells(lrow, 1).Value = WorksheetFunction.Max(.ListColumns(1).Range) + 1
        .DataBodyRange.Cells(lrow, 2).Value = ExpIncDrop.Value
        If ExpIncDrop.Value = "Expense" Then
            .DataBodyRange.Cells(lrow, 3).Value = TypeDrop.Value
        Else
            .DataBodyRange.Cells(lrow, 4).Value = TypeDrop.Value
        End If
        .DataBodyRange.Cells(lrow, 5).Value = DateTxt.Value
        .DataBodyRange.Cells(lrow, 6).Value = AmountTxt.Value
        .DataBodyRange.Cells(lrow, 7).Value = RemarksTxt.Value
    End With

 

[Robby19]

Works beautifully. Thank you!

 

[Robby19]

Here's another one for you. Since the spreadsheet will be using worksheet protection, and the userforms are set up to remove the protection so long as they are active, how would I write code to edit a transaction? I created another Userform that requires the user to type the transaction # and then click button ImportDataBtn.

This should then look at the active sheet, find the corresponding transaction, and pull the data into the Userform. The user can then edit the data as they deem necessary and click Save. Thus changing the transaction in the spreadsheet and activating the protection again.

There will be more to add on to this, however I may be able to work that part out even with my very limited skills.

 

[Robby19]

I feel like I am close-ish.

Dim FindThis As String
Dim LastColumn, LastRow As Long
Dim rw, FirstAddress, LastCell, SrchRnge

UserForm2.TransTxt = ""

LastRow = Range("Bookkeeping").Row + Range("Bookkeeping").Rows.Count - 1
LastColumn = Range("Bookkeeping").Column + Range("Bookkeeping").Columns.Count - 1
LastCell = Range("Bookkeeping").SpecialCells(xlCellTypeLastCell).Address
SrchRnge = "$A$4:" & LastCell

FindThis = UserForm2.TransTxt.Value

With Sheet3.Range(SrchRnge).Cells

Set rw = .Find(What:=FindThis, After:=Sheet3.Range("$A$4"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

End With

On Error GoTo notfound
notfound:

MsgBox (FindThis & " is not a valid Transaction #.")

 

[My Aswer Is This]

Here is how I add data to a Table:
Now in my example I use Me You Us

As if your dealing with data from a Textbox you would use:
Textbox1.value Textbox2.value TextBox3.Value for example.

Sub My_Table()
'Modified  9/20/2018  2:00:46 AM  EDT
Dim ans As Long
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        ans = .Rows.Count + 1
        .Cells(ans, 1).Value = "Me"
        .Cells(ans, 2).Value = "You"
        .Cells(ans, 3).Value = "Us"
    End With
End Sub

 

[Robby19]

Appreciate the response! Any idea how to do the second part?

 

[My Aswer Is This]

And the second part being putting values in column 1 of the table is that what your asking about?
In the last post were you referring to my Post?

 

[Robby19]

Referring to Post #4 and #5 . What it looks like to me, a complete novice so I may be wrong, but in your post #6 you are transferring data FROM a userform TO a table. On my second question I am trying to figure out how to do the opposite. Basically typing a unique number into a textbox, clicking a command button and then have the userform populate the data displayed in that specific row tied to the unique number.

I could then change the data displayed in the userform, and using your code, overwrite the current data in the table with the new data in the userform.

Hope this makes sense.

 

[My Aswer Is This]

To advance the number in column 1 of your table I would suggest using this.
Put the value 1 into Range("A1")

Now every time the script runs the number in Range("A1") will advance by 1 and this value will be the running total which will referred to next time the script runs

Use this script:

Sub My_Table()
'Modified  9/20/2018  2:25:46 AM  EDT
Dim ans As Long
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        ans = .Rows.Count + 1
        .Cells(ans, 1).Value = Cells(1, 1).Value + 1: Cells(1, 1).Value = Cells(1, 1).Value + 1
        .Cells(ans, 2).Value = "Me"
        .Cells(ans, 3).Value = "You"
        .Cells(ans, 4).Value = "Us"
    End With
End Sub