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]

.
I amended the code as shown below :

Private Sub cmdAddData_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 = "x"
    ws.Cells(newRow, 2).Value = Me.TextBox1.Value
    ws.Cells(newRow, 3).Value = Me.TextBox2.Value
    ws.Cells(newRow, 4).Value = Me.TextBox3.Value
    ws.Cells(newRow, 5).Value = Me.TextBox4.Value
    ws.Cells(newRow, 7).Value = Me.TextBox6.Value
    ws.Cells(newRow, 8).Value = Me.TextBox7.Value
    ws.Cells(newRow, 9).Value = Me.TextBox8.Value
    ws.Cells(newRow, 10).Value = Me.TextBox9.Value


End Sub

The issue lies with Col A.

Using the above method of entering data to a worksheet requires that ALL columns beginning with Col A and moving to the
right until the last Col of data must have some type data within. In your scenario, you want Col A to be empty for visual
appearance reasons ... which is understandable and ok. However, for the above code to function as expected even Col A
must have something in it. I formatted all of Col A to be centered and the text color to be WHITE so it blends with the cell
background. Hence ... the user cannot see anything in Col A cells.

The code now enters the letter " x " as a space holder for Col A cells .. then writes the remaining data to the following columns
on that row.


Explain why you have the same entry showing twice on each row ? What is the purpose ?



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

 

[Logit]

.
Also it is note worthy that your workbook is an unusually large size (mb wize). I'm not completely certain what is occurring or why this is happening but
your workbook should not be approx. 5 mb in size at this time. There is hardly anything in it.

I attempted to clean out any spurious unseen data that might have existed in the workbook but that did not reduce the size.

Not certain what other remedy you can take other than to build a completely new workbook at this time BEFORE the project gets too large and makes
recreation untenable. When you recreate the workbook DO NOT copy and paste from this existing workbook to the new workbook as you will only be
copying the same error/s to the new book.

 

[kalumbair]

Logit,

You're not going to believe this, it works but.. it will only pasted the data on row 2 of the worksheet and the last row. I think you right, this workbook is dirty from all the copy and paste I've done since starting this project. I need to start new workbook. I'll keep you updated on my progress. thank you so much for your patience.
K

 

[kalumbair]

Logit,

You're not going to believe this, it works but.. it will only pasted the data on row 2 of the worksheet and the last row. I think you right, this workbook is dirty from all the copy and paste I've done since starting this project. I need to start new workbook. I'll keep you updated on my progress. thank you so much for your patience.
K