Copy textbox lines to sheet2

[Jaye7]

I am after a script that would copy all the lines/ rows in a textbox to sheet2 column B.

the textbox data would be in rows/ lines as follows, 1 column only, but there could be up to 5000 lines/ rows.

AAA
BBB
CCC
DDD
EEE
FFF
GGG

 

[xenou]

here is some sample code in your userform (edit/adapt to suit). Note: it is assumed that the rows in the textbox have newline characters -- i.e., you've entered Control + Enter after each value or something of the sort. This is at best odd and at worst a poor choice for storing data - textboxes are not meant to store many rows of data in a structured manner - though you've certainly got me wondering how you end up with one that does. Can you not put this data on a spreadsheet to begin with?

Private Sub CommandButton1_Click()
Dim a As Variant
    a = Split(Me.TextBox1.Value, vbNewLine)
    Sheet2.Range("B1").Resize(UBound(a) + 1, 1).Value = Application.Transpose(a)
End Sub

<a href="http://northernocean.net/etc/mrexcel/20100814_ufsample.zip">Sample Workbook (zip format)</a>

 

[Nalani]

Reading through Posts, just opened this one. Don't have an answer, (BUT

xenou
How did you attach a file in the fashion that you did with a link. I thought the Board did not have the option to attach a File directly?

You seemed to have found a way to do it.

 

[Jaye7]

Xenou,

Thanks for yet another script, however the script that you gave me is bugging out on the following line.

code:
Sheet2.Range("B1").Resize(UBound(a) + 1, 1).Value = Application.Transpose(a)

There is a sheet2 in my workbook and even it I change the sheet name reference to a different sheet is is still bugging out.