Dave,
If you want to have the control of the lines length independent of the Wraptext property of a range, here you are:
Code:
Function Wrapped(ByVal strText As String, intChars As Integer) As String
'Split the strText in lines with length <= intChars
Dim strRet As String
Dim strLf As String
Dim intPos As Integer
If intChars > 0 Then
If Len(strText) Then
While intChars < Len(strText)
strLf = vbLf
intPos = InStrRev(strText, " ", intChars)
If intPos = 0 Then
intPos = intChars
strLf = "-" & strLf 'Brake word
End If
strRet = strRet & Trim(Left(strText, intPos)) & strLf
strText = Mid(strText, intPos + 1)
Wend
strRet = strRet & strText
End If
Wrapped = strRet
End If
End Function
In Excel, you may use this function as seems below:
Code:
Sub WrapClientNote(intChars As Integer, Optional MultiRow As Boolean)
'Split the text of a cell in lines
Dim x As Variant
Dim r As Range
Set r = Worksheets(1).Range("A30")
If Len(r) Then
x = Wrapped(r, intChars)
If MultiRow Then
'Lines in rows
x = Split(x, vbLf)
r.Offset(2).Resize(UBound(x) + 1) = Application.WorksheetFunction.Transpose(x)
Else
'All wrapped text in one cell
r.Offset(2) = x
End If
End If
End Sub
The procedure above, could be a method of a template workbook.
Make a test:
Cheers,
John