Insert a line break after word wrap

[Av8tordude]

I have a multi-line textbox I want to insert a line break after word-wraps to a new line. Can someone help with a vba code that can do this.

Thank you kindly

 

[william_man]

The vba for a new line is vbCrLf

UserForm1.TextBox1.Value = "Line 1" & vbCrLf & "Line 2"

Also make sure that the MultiLine property of the TextBox is set to True.

 

[Av8tordude]

The vba for a new line is vbCrLf

UserForm1.TextBox1.Value = "Line 1" & vbCrLf & "Line 2"

Also make sure that the MultiLine property of the TextBox is set to True.

I know vbCrlf inserts a new line, I guess I did not explain it very well what I wouldl like to do. When I type in the text box, the last word automatically wraps to the next line. When I click a button to enter the string in a cell, it enters a 1 single line.

What I asking, I would to either...

1. when the last word wraps to the next line in the textbox, insert a line break in the previous line or
2. a code that will enter line breaks after each line so that the string will not be 1 string in the cell

Thanks

 

[Paul Ked]

X-posted here http://www.vbaexpress.com/forum/showthread.php?66200-Insert-a-line-break-after-word-wrap

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

You might understand that some helpers may become disgruntled if they put time into developing a solution here only to find later that a solution had already been achieved elsewhere. They may then be less likely to help you next time.

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[Kenneth Hobson]

I used Word to do what you want. See: https://www.mrexcel.com/forum/excel-questions/1022360-fitting-text-listbox.html

 

[Jaafar Tribak]

You could use this hack : (should work with multi-line textboxes on userforms as well as in worksheets)


1- In a Standard Module :

Option Explicit


Public Function GetMultilineTextBoxText(ByVal TextBox As Object, Optional ByVal TrimLeft As Boolean) As String

    Dim I As Long, lLineCount As Long, lNewSelLength, lNewSelStart As Long, lPrevX As Long
    Dim sLinesArray() As String
    Dim sLineText As String

    On Error Resume Next
        CallByName TextBox, "Activate", VbMethod
        CallByName TextBox, "SetFocus", VbMethod
    On Error GoTo 0
    
    With TextBox
        If .LineCount > 1 Then
            lNewSelStart = 0
            For I = 1 To Len(.Text) + 1
                .SelStart = lNewSelStart
                .SelLength = lNewSelLength
                sLineText = .SelText
                If .CurTargetX < lPrevX Then
                    ReDim Preserve sLinesArray(lLineCount)
                    sLinesArray(lLineCount) = IIf(TrimLeft, LTrim(sLineText), sLineText)
                    lNewSelStart = I - 1
                    lNewSelLength = 0
                    lLineCount = lLineCount + 1
                End If
                lPrevX = .CurTargetX
                lNewSelLength = lNewSelLength + 1
            Next I
            If lLineCount Then
                ReDim Preserve sLinesArray(UBound(sLinesArray) + 1)
                sLinesArray(UBound(sLinesArray)) = .SelText
            Else
                GetMultilineTextBoxText = Replace(.Text, vbLf, "")
                Exit Function
            End If
            .SelStart = 0
            .SelLength = 0
            Selection.Select
            GetMultilineTextBoxText = Join(sLinesArray, vbLf)
        Else
            GetMultilineTextBoxText = .Text
        End If
    
    End With
    
End Function

2- Code Usage example:

Sub TEST()
    Range("A1") = GetMultilineTextBoxText(TextBox1)
    Debug.Print GetMultilineTextBoxText(TextBox1)
End Sub