Userform - strip text from textbox to seperate textboxes...

[RobbieC]

Hi there, I have a problem which has been bugging me for ages...

I have a userform with a multiline text box (limited to 400 chars).

I also have 4 seperate text boxes (not visible to the user on the form)

What I'm trying to do is strip the large multiline-textbox into the 4 smaller text boxes. BUT I want to keep the sentance structure ie. not just chop out the first 100 chars as it could be half way through a word!

So, in essence, I'm looking to look at the first 100 chars, then find the last complete word and copy that series of words into the first smaller textbox
Then do the same with the second smaller text box, but start from where the first one left off... up to smaller textbox number 4
If the user types in a blank line (enter key twice), I don't need that. Not interested in keeping any paragraph formatting - only the actual words that the user types...

I hope I've described this scenario ok.

I tried to use something along this line:

    Dim strMultiLineData, x As Integer
    strMultiLineData = Split(Me.description, vbCrLf)
    For x = LBound(strMultiLineData) To UBound(strMultiLineData)
        Me.Controls("TextBox" & x + 1) = strMultiLineData(x)
        'Debug.Print strMultiLineData(x) & " --- " & x
    Next x

this separates the multiline textbox rows as you type into x number of textboxes for each line. It does work to a degree, but it does it all in real-time and I've noticed that it's prone to errors, such as if you paste a series of words (or a long word) which then move to the next line, or if you delete text back to a previous line

I hope you can understand where I'm coming from. If you can help me out, I'd be most grateful

Thanks

 

[Rick Rothstein]

Here is a function that you can use to split out the text in maximum 100-character chunks...

[table="width: 500"]
[tr]
	[td]Function Text100Chars(S As String) As String()
  Dim N As Long, Space As Long, TextMax As String, Text As String, Arr(1 To 4) As String
  Const MaxChars As Long = 100
  Text = Application.Trim(Replace(S, vbLf, " "))
  Do While Len(Text) > MaxChars
    N = N + 1
    TextMax = Left(Text, MaxChars + 1)
    If Right(TextMax, 1) = " " Then
      Arr(N) = RTrim(TextMax)
      Text = Mid(Text, MaxChars + 2)
    Else
      Space = InStrRev(TextMax, " ")
      If Space = 0 Then
        Arr(N) = Left(Text, MaxChars)
        Text = Mid(Text, MaxChars + 1)
      Else
        Arr(N) = Left(TextMax, Space - 1)
        Text = Mid(Text, Space + 1)
      End If
    End If
  Loop
  Arr(N + 1) = Text
  Text100Chars = Arr
End Function[/td]
[/tr]
[/table]

The most efficient way to use it is to declare an array within your own code and then assign the output from my function to it. Here is a test sample to give you the idea of what to do in your own code...

Sub Test()
  Dim N As Long, MyArr As Variant
  MyArr = Text100Chars(YouMultiLineTextBox.Text)
  TextBox1.Text = MyArr(1)
  TextBox2.Text = MyArr(2)
  TextBox3.Text = MyArr(3)
  TextBox4.Text = MyArr(4) 
End Sub

Of course you can use a loop to distribute the text to the TextBoxes, I just went for something quick and dirty before I head off to sleep.

 

[RobbieC]

Cheers Rick, once again you come up trumps!

I shall give it a whirl and report back

Thanks

 

[RobbieC]

Works like a dream! Thanks Rick!

 

[Rick Rothstein]

Works like a dream! Thanks Rick!

You are quite welcome, however, I think there might be a "flaw" in your UserForm's layout. Your multiline TextBox can hold a maximum of 400 characters and each of your smaller TextBoxes can hold a maximum of 100 characters. What if you or your program's user places something very near to 400 characters into the multiline TextBox? The odds are that the 100th character in each smaller TextBox will not end at an exact word. That means that at minimum, one word to four words would be left over after the 4th TextBox is finished being filled... you do not have a 5th TextBox to take the overflow which means some of the text at the end of the nearly filled multiline TextBox would be lost.

 

[RobbieC]

Good thinking Rick, I shall have a re-design of the layout and probably shorten the main multi-line textbox as well as add a fifth 'overflow' textbox for good measure!

Thanks again for your input mate