Copy and paste data from userform textboxes to worksheet

[kalumbair]

Hello Everyone,

Can someone kindly help meas I’m new to VBA. I have a userformthat contains a listbox with several lines of data. When the user double clicks on a row in thelistbox, it populates a series of textboxes below. Right now, I can’t seem to get the ADD bottomto work. What I’d like to do is when theuser clicks on the ADD button the data that was double clicked into thetextboxes will copy to the last row of the worksheet. And after the data is pasted to theworksheet, I want it to clear from the textboxes. If anyone can help out, I’d really appreciateit. The code and link to the worksheet I’mworking with is below. Thank you inadvance.

https://www.dropbox.com/s/yenbsr2yn5z1aan/Customer Tracker.xlsm?dl=0

 Private Sub cmdAddData_Click()

If TextBox1.Value ="" Or TextBox2 = "" Or TextBox3 = "" Then
If MsgBox("Data isIncomplete. Do you Want to Continue?", vbQuestion + vbYesNo) <>vbYes Then
    Exit Sub
    End If
End If
ActiveCell = TextBox1.Value
ActiveCell.Offset(0, 1) =TextBox2.Value
ActiveCell.Offset(0, 2) =TextBox3.Value
ActiveCell = TextBox6.Value
ActiveCell.Offset(0, 6) =TextBox7.Value
ActiveCell.Offset(0, 7) =TextBox8.Value
ActiveCell.Offset(1,5).Select
Call resetform
End Sub

 

[Logit]

.
Here is an example of what you are seeking. As the comments in the code indicate, you can add additional textboxes to the existing
macro so it includes all of your textboxes.

Study the code and if you have difficulty let me know.

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/V6oHsruBAmvZLayk7Mdd8GSDHnHruGTR1LGIy1qH9G1

 

[kalumbair]

hi Logit,

none of these examples supports what i'm trying to do. thank you for the response though!

v/

 

[Logit]

.
The macro example provided will do what you are seeking. Unless of course you misspoke and the goal is nothing
like you described.

Also ... you will need to edit the code slightly to match your circumstances (name of textboxes, etc.) . It is presumed
based on your existing code that you are sufficiently knowledgeable to code ?

 

[kalumbair]

Logit,

Sorry for the late reply, I had to go to this thing callwork. Lol. So was able to get it to work, and it will paste to the last rowonce, but any time after that it continues to paste to the same row. The codeis below.

 Private Sub cmdAddData_Click()

    Dim ws AsWorksheet
    Set ws =ActiveSheet
    Dim newRow AsLong
    newRow =Application.WorksheetFunction.CountA(ws.Range("B:B")) + 1
    'The next twolines can be expanded as many times as needed for all the entry fields in yourproject
   ws.Cells(newRow, 1).Value = Me.TextBox1.Value
   ws.Cells(newRow, 2).Value = Me.TextBox2.Value
   ws.Cells(newRow, 3).Value = Me.TextBox3.Value
    ws.Cells(newRow, 4).Value =Me.TextBox4.Value
   'ws.Cells(newRow, 6).Value = Me.TextBox6.Value
   ws.Cells(newRow, 7).Value = Me.TextBox7.Value
   ws.Cells(newRow, 8).Value = Me.TextBox8.Value
   ws.Cells(newRow, 9).Value = Me.TextBox9.Value
 
    End Sub 

 

[Logit]

.
Change this line :

[COLOR=#000000][FONT=&quot]newRow =Application.WorksheetFunction.CountA(ws.Range("B:B")) + 1[/FONT][/COLOR]

To this :

[COLOR=#000000][FONT=&quot]newRow =Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1[/FONT][/COLOR]

It should work as expected ... as you can verify in the sample workbook download.

 

[kalumbair]

the weird thing is it doesn't work with "A:A", it works with "B:B" but it doesn't paste it on the last row. it's paste on second row where data already lives. its there a different way to write the code perhaps? thanks again for your time.

 

[Logit]

.
If it doesn't work as designed there is something else going on with your workbook.

Are you willing to post your workbook so it can be downloaded and reviewed ?

 

[kalumbair]

Logit,

it's good to hear back from you. below is the link to the WB.

https://www.dropbox.com/s/2kn5f6li4h93tft/BUDGET_31jul2019.xlsm?dl=0

K

 

[kalumbair]

Logit,
I’m sorry I didn’t provide and explanation. So the idea is that when the user doubleclicks on a row from the listbox, it populates the textboxes below. The user can then either EDIT DATA, DELETEDATA or this case add the data as a new row. Thank you again.

K