Insert a line break after word wrap

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
1,074
Office Version
  1. 2019
Platform
  1. Windows
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

 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top