Insert a line break after word wrap

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
924
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

 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

william_man

New Member
Joined
Oct 31, 2019
Messages
19
The vba for a new line is vbCrLf
Code:
UserForm1.TextBox1.Value = "Line 1" & vbCrLf & "Line 2"
Also make sure that the MultiLine property of the TextBox is set to True.
 

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
924
The vba for a new line is vbCrLf
Code:
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,156
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,929
Office Version
  1. 2016
Platform
  1. Windows
You could use this hack : (should work with multi-line textboxes on userforms as well as in worksheets)


1- In a Standard Module :
Code:
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:
Code:
Sub TEST()
    Range("A1") = GetMultilineTextBoxText(TextBox1)
    Debug.Print GetMultilineTextBoxText(TextBox1)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,517
Messages
5,523,349
Members
409,512
Latest member
Exceldoktor

This Week's Hot Topics

Top