Split long text with max 30 characters keeping words intact to multiple rows using vb

[newbi004]

Hi

I have a textbox on a userform where a long sentence can be inputted. This text then needs to be inserted in a column on a spreadsheet page that is a maximum of 30 characters wide. I need to develop a macro that will split the text, keeping words intact, to be inserted into a number of consecutive rows in a column. As an example, using the sentences in this post, it would look like this:

I have a textbox on a userform
where a long sentence can be
inputted. This text then
needs to be inserted in a
column on a spreadsheet page
that is a maximum of 30
characters wide. I need to
develop a macro that will
split the text, keeping words
intact, to be inserted into a
number of consecutive rows in
a column. As an example,
using the sentences in this
post, it would look like this:

Any thoughts?

Cheers

[nilem]

try something like this

Please Login or Register  to view this content.

[BigBas]

Enter the following code wherever you are firing off your macro and let me know if it helps.

Please Login or Register  to view this content.

[mikerickson]

Please Login or Register  to view this content.

[shg]

@Mike: Think you're missing a Len function.

@Nilem: Short and sweet.

[newbi004]

Thanks all of you!

BigBas, your code did split the long sentences to the required max, but unfortunately split some words as well. I was looking to keep the words intact.

Mike, your code split the sentence into individual words. I will be able to use both your codes for other situation...however,

Nilem...I just don't understand your code, but it did exactly what I wanted!!!!! Could I trouble you to adapt the code to split to a max of, say 46 characters? Could I wait for your response before I mark this thread as SOLVED?

Thanks to all again.

[mikerickson]

Yes, I was missing a Len. I also failed to account for the user entering a line feed in the TextBox

Please Login or Register  to view this content.

[newbi004]

Wow, what a fantastic group of individuals in this Forum!!

Mike, your code now works a treat. I am relatively a beginner in vb (newborn compared to you guys!), so would very much appreciate a line by line explanation of your code (if you have the time or inclination).

Nilem's code also works but is a complete mystery (for me, anyway) to understand how it can be adapted for different parameters (eg different max lengths).

Anyway, I am going to mark this thread as solved.

Cheers to all.

[shg]

but is a complete mystery (for me, anyway) to understand how it can be adapted for different parameters (eg different max lengths).

It splits the string according to the width of the column, with consideration for font face and font size in each cell. You can't specify the length.

[mikerickson]

Dim oneWord as Variant, oneParen as Variant
Dim oneLine as String

Please Login or Register  to view this content.

[newbi004]

Thanks for taking the time to explain your code, Mike. It is very useful to know this so I can use it for different circumstances.

shg, thanks also for explaining Nilem's code. I realised that by chance when I increased the column width, but good to get confirmation.