Text length and TextBoxes

[Guest]

Thanks in advance for all your help...
I have a userform that has a textbox that is set to display multiple lines
of input via a cut and paste operation. Once I have pasted the text into the
textbox, I'd like for it to be broken up into an array of text with lengths
less than or equal to 250. Can you guys point me in the right direction?

 

[Dick Kusleika]

Ron

I'm not sure if I totally understand, but let's start with this

Dim i As Long, j As Long
Dim aText() As String
Dim lLen As Long

lLen = Len(Me.TextBox1.Text)

ReDim aText(1 To (lLen \ 250) + 1)

For i = 1 To lLen Step 250
j = j + 1
aText(j) = Mid(Me.TextBox1.Text, i, 250)
Next i

Now aText will be an array of strings not greater than 250 characters each.

 

[Guest]

Hi Dick,

That's a start. I'm trying to breakup the very long text into shorter
strings and storing these shorter strings on cells in a spreadsheet using a
loop method to write the strings to these cells or stepping through an array.

My goal is to catalog merge these cells into Publisher, but the merge
command truncates any string that is greater than 255 characters. So each
cells would have a portion of the original string. For example, the original
text is 1270 characters long. There would need to be 6 cells. The first
cell would have the 1st 250 characters, the 2nd cell would have characters
251 to 500, etc etc.

I hope this clears up what I need. Thanks again,
Ron

 

[Dick Kusleika]

Ron

Dim i As Long, j As Long

For i = 1 To Len(Me.TextBox1.Text) Step 250
j = j + 1
Sheet1.Cells(j, 1).Value = Mid(Me.TextBox1.Text, i, 250)
Next i


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

 

[Guest]

Thanks Dick for all your help!!!! The code you gave me worked perfectly.

Ron