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
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.
You could use this hack : (should work with multi-line textboxes on userforms as well as in worksheets)
1- In a Standard Module :
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
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
lPrevX = .CurTargetX
lNewSelLength = lNewSelLength + 1
If lLineCount Then
ReDim Preserve sLinesArray(UBound(sLinesArray) + 1)
sLinesArray(UBound(sLinesArray)) = .SelText
GetMultilineTextBoxText = Replace(.Text, vbLf, "")
.SelStart = 0
.SelLength = 0
GetMultilineTextBoxText = Join(sLinesArray, vbLf)
GetMultilineTextBoxText = .Text
2- Code Usage example:
Range("A1") = GetMultilineTextBoxText(TextBox1)