Splitting userform text onto different rows

[achar]

I have a requirement with a macro that it should post to consecutive adjacent columns. I am a complete novice in VBA.


Let me explain: I take the user input using a Userform Textbox.


Next after clicking on the submit button, It should post the text inputted to Column A with 200 characters per row.


Suppose 500 characters were inputted into the Userform Text box, after clicking on the submit button, A1 must have 200 characters, A2 must have 200 characters and A3 must have 100 characters.

Please help me out with this as earlly as possible.

So far I have tried the Split function but to no avail since it uses a delimiter to differentiate characters and not the number of characters.

 

[Momentman]

Assuming the button is called "SubmitBtn" and the Textbox is called "TextBox1", you can add this code to the submit button

Private Sub SubmitBtn_Click()
    LenText = Len(Me.TextBox1.Text)
    j = 1
    I = 1
    Do
        Range("A" & j) = Mid(Me.TextBox1.Text, I, 200)
        I = I + 200
        j = j + 1
    Loop While I < LenText
End Sub

 

[Ingolf]

Cros Post

Splitting userform text onto different rows

 

[achar]

Momentman, you code worked beautifully. Thank you!
Igolf, sorry 'bout it. I did search the forum but couldnt find something like this.

 

[mikerickson]

To avoid splitting the string in the middle of words, code like this can be used.

Private Sub butSubmit_Click()
    Dim Sentence As String, firstPart As String
    
    Sentence = Me.TextBox1.Text
    Sentence = Replace(Sentence, vbLf, " ")
    Sentence = Replace(Sentence, vbCr, " ")
    
    
    Do Until Sentence = vbNullString
        firstPart = DelimitedLeft(Sentence, 200)
        
        Sheet1.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = firstPart
        
        Sentence = Trim(Replace(Sentence, firstPart, vbNullString, 1, 1))
    Loop
    
End Sub

Function DelimitedLeft(strSentence, Length As Long, Optional Delimiter As String = " ") As String
    Dim Words As Variant
    DelimitedLeft = WorksheetFunction.Trim(Left(strSentence, Length + 50))
    
    Do
        Words = Split(DelimitedLeft, Delimiter)
        If 0 < UBound(Words) Then
            ReDim Preserve Words(0 To UBound(Words) - 1)
            DelimitedLeft = Join(Words, Delimiter)
        Else
            Exit Do
        End If
    Loop Until Len(DelimitedLeft) <= Length
End Function