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

 

[My Aswer Is This]

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.

And this unique number would be the number in column 1 of the Table is that true.
That could be done but for me to do that I would need to know which of the above scripts you decide to use.
And if not mine maybe we should wait and see what the previous posters want to try.
I understand my script but not theirs
And I always like knowing the name of the Table
In your original post you did not give the Table Name

 

[Robby19]

I am willing to use anything that works. This is a learning experience for me.

As far as the table information, you are correct, the 1st column has the Unique numbers.

Table Name - Bookkeeping
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Transaction #
[/TD]
[TD]Type
[/TD]
[TD]Expense Type
[/TD]
[TD]Income Type
[/TD]
[TD]Date
[/TD]
[TD]Amount
[/TD]
[TD]Remarks
[/TD]
[TD]Reason for Editing
[/TD]
[TD]TypeDrop
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Formula
[/TD]
[/TR]
</TBODY>[/TABLE]

TypeDrop formula;

=IF([@[Expense Type]]<>"",[@[Expense Type]],[@[Income Type]])

The reason for the TypeDrop column with formula is due to the Type and Expense & Income Type. When data is entered from the New Transaction form, there is a drop down selection for Expense and Income. If Expense is selected, a dependent dropdown is provided and that data is transferred to the Expense Type column. Same goes for the Income Type column if Income is selected.

I am thinking about also adding a Date Edited column. So that when the user submits the Edit Transaction Userform, excel automatically pulls the current Date/Time and puts it in the cell.

 

[Robby19]

In my mind this is how I think it should play out;

1. User clicks Edit Transaction Button, and Edit Transaction Userform pops-up
2. User types the Transaction # into the Userform and clicks the Import Data button
3. Excel does its thing and populates the Userform with Type, Expense/Income Type, Date, Amount, Remarks
4. User makes whatever changes they need to along with filling out the Reason for Editing textbox
5. User clicks Save button and the data in the table gets overwritten by the data in the userform for that transaction.
6. Excel also adds the Date/Time to the Date Edited column for that transaction

 

[My Aswer Is This]

In my mind this is how I think it should play out;

1. User clicks Edit Transaction Button, and Edit Transaction Userform pops-up
2. User types the Transaction # into the Userform and clicks the Import Data button
3. Excel does its thing and populates the Userform with Type, Expense/Income Type, Date, Amount, Remarks
4. User makes whatever changes they need to along with filling out the Reason for Editing textbox
5. User clicks Save button and the data in the table gets overwritten by the data in the userform for that transaction.
6. Excel also adds the Date/Time to the Date Edited column for that transaction

This is a viable way to do this. But for me to help you more I would need to know if my last script worked for you.
And I asked you about table name and you gave me same name as sheet name. Please once again provide sheet name and Table name.

 

[sericom]

You could try something like this on your import button click event. You'll need to adjust Userform1 to the name of your data entry form, and also txtTransactionNo to the name of your textbox that has the number to find..

    Dim fCell As Range
    Set fCell = Sheets("Bookkeeping").ListObjects("Table1").ListColumns(1).Range.Find(txtTransactionNo, LookIn:=xlValues, lookat:=xlWhole)
    If Not fCell Is Nothing Then
        With UserForm1
            .ExpIncDrop = fCell.Offset(, 1)
            .TypeDrop = fCell.Offset(, 2) & fCell.Offset(, 3)
            .DateTxt = fCell.Offset(, 4)
            .AmountTxt = fCell.Offset(, 5)
            .RemarksTxt = fCell.Offset(, 6)
    Else
        MsgBox "Transaction not found"
    End If

 

[Robby19]

I had to add an End With to your code Sericon, or else it gave me an error.

Private Sub ImportDataBtn_Click()
'Modified  9/20/2018
    Dim fCell As Range
    Set fCell = Sheets("Bookkeeping").ListObjects("Bookkeeping").ListColumns(1).Range.Find(TransTxt, LookIn:=xlValues, lookat:=xlWhole)
    If Not fCell Is Nothing Then
        With UserForm1
            .ExpIncDrop = fCell.Offset(, 1)
            .TypeDrop = fCell.Offset(, 2) & fCell.Offset(, 3)
            .DateTxt = fCell.Offset(, 4)
            .AmountTxt = fCell.Offset(, 5)
            .RemarksTxt = fCell.Offset(, 6)
        End With
    Else
        MsgBox "Transaction not found"
    End If

While this does not give me an error anymore, it also does not pull the table data from the spreadsheet into the userform. It recognizes transaction # "1" from the table, but the userform stays blank. Also, if runs the MsgBox when it cannot find the transaction so that is all working properly.

The sheet name and table name are both Bookkeeping

 

[sericom]

If you're happy to send a sample copy of your workbook I can have a look.
Sent you a message with my email address.

 

[sericom]

I assumed you would be calling Userform2 from Userform1, that's why it didn't work. You still need to do the save code, yell out if you get stuck.

Private Sub ImportDataBtn_Click()
'Modified  9/20/2018
    Dim fCell As Range
    Set fCell = Sheets("Bookkeeping").ListObjects("Bookkeeping").ListColumns(1).Range.Find(TransTxt, LookIn:=xlValues, lookat:=xlWhole)
    If Not fCell Is Nothing Then
        With [COLOR=#ff0000]UserForm2[/COLOR]
            .ExpIncDrop = fCell.Offset(, 1)
            .TypeDrop = fCell.Offset(, 2) & fCell.Offset(, 3)
            .DateTxt = fCell.Offset(, 4)
            .AmountTxt = fCell.Offset(, 5)
            '.RemarksTxt = fCell.Offset(, 6)
        End With
    Else
        MsgBox "Transaction not found"
    End If

 

[Robby19]

Hooray! The data is successfully pulled into the userform. Yes, now I need to create the code for the save buttons.

 

[Robby19]

So here is my first take on the Transfer Data Save button code.

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(lrow, 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

At first glance it worked fine, however it also replaced the data for transaction 1. Example;
Original
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Transaction #
[/TD]
[TD]Type
[/TD]
[TD]Expense Type
[/TD]
[TD]Income Type
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Expense
[/TD]
[TD]Cargo Claims
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Income
[/TD]
[TD][/TD]
[TD]Cash
[/TD]
[/TR]
</TBODY>[/TABLE]

I edited Transaction 2 with the userform and it added the income type to Transaction 1, vice transaction 2. Any idea?
After running the userform
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Transaction #[/TD]
[TD]Type[/TD]
[TD]Expense Type[/TD]
[TD]Income Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Expense[/TD]
[TD]Cargo Claims[/TD]
[TD]Investment[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Income[/TD]
[TD][/TD]
[TD]Cash[/TD]
[/TR]
</TBODY>[/TABLE]

Trans 1 should be Expense, Cargo Claims & Trans 2 should be Income, Investment