Jenny comments
My question is, if your text line is *really* long and spills over the page margin, how do you make it fit on the page (in two or three lines of text) without wrapping all the text into one very tall cell (i.e. a narrow column)? I haven’t been able to find out how to do this and it’s driving me crazy. Any ideas? Thanks, and since I am a beginner, I need very specific info.
I think Jenny wants Edit > Fill > Justify, like this
You may note the problem with this feature is that it only works on the first 255 characters of the text. I have my own Justify procedure, but it differs from the built in function because it doesn’t alert you when your text is going to extend beyond the selection. I tried to modify it so I could post it here, but I had problems with it.
Dim sContents As String
Dim lMaxLen As Long
Dim rCell As Range
Dim i As Long
Dim lCut As Long
Dim lResp As Long
If TypeName(Selection) = “Range” Then
If Not IsEmpty(Selection.Cells(1).Value) Then
sContents = Selection.Cells(1).Text
Application.DisplayAlerts = False
Selection.Justify
For Each rCell In Selection.Columns(1).Cells
If Len(rCell.Text) > lMaxLen Then
lMaxLen = Len(rCell.Text)
End If
Next rCell
Application.DisplayAlerts = True
Selection.Columns(1).ClearContents
lResp = vbOK
If Len(sContents) / lMaxLen > Selection.Rows.Count Then
lResp = MsgBox(“Text will extend below the selected range.”, vbExclamation + vbOKCancel)
End If
If lResp = vbOK Then
Do Until Len(sContents) < lMaxLen
i = i + 1
lCut = InStrRev(sContents, Chr$(32), lMaxLen, vbTextCompare)
If lCut = 0 Then
lCut = lMaxLen
End If
Selection.Columns(1).Cells(i).Value = Left$(sContents, lCut)
sContents = Right$(sContents, Len(sContents) – lCut)
Loop
Selection.Columns(1).Cells(i + 1).Value = sContents
Else
Selection.Cells(1).Value = sContents
End If
End If
End If
End Sub
I use the built in Justify method to determine what the maximum length of the line should be. It’s not perfect, but it seemed like an easy way to find where to split the text. I surround it with DisplayAlerts properties because I need to control the message about text that’s too long. Because I’m not truncating at 255 characters, what I consider too long and what Excel does may be different.
The problem that I have is that the built in Justify might overwrite data before the user has a chance to cancel. Even if it doesn’t overwrite data, if the user cancels it still may leave an orphan sentence or two. To see what I mean, put a sufficiently long string in A1, select A1:C3, then click Cancel when prompted.
I need a better way to find lMaxLen than the Justify method.
I needed a “Long Justify” command a while back and did it a bit differently.
First, I go through the selected data and break each line into 255 byte or less portions (so that the EXCEL limitation is not exceeded). For each piece that exceeds 255 bytes, I insert a new column range identical to the original range width and place the overflow data there, then repeat until I reach the end of the selected range. Then I just run the normal justify function.
I actually have the overlay alert turned off, but it could be left on.
======================================================
Public Sub usJustifyWithLongText()
iRow = Selection.Row
iCol = Selection.Column
iRows = Selection.Rows.Count
iCols = Selection.Columns.Count
iEnd = iRow + iRows – 1
i1 = iRow
Do While i1 255 Then
Range(Cells(i1 + 1, iCol), Cells(i1 + 1, iCol + iCols – 1)).Insert Shift:=xlDown
iPos = InStrRev(Cells(i1, iCol).Value, ” “, 255)
Cells(i1 + 1, iCol) = Mid(Cells(i1, iCol).Value, iPos + 1)
Cells(i1, iCol) = Mid(Cells(i1, iCol).Value, 1, iPos – 1)
iEnd = iEnd + 1
End If
i1 = i1 + 1
Loop
Range(Cells(iRow, iCol), Cells(iEnd, iCol + iCols – 1)).Select
Application.DisplayAlerts = False
Selection.Justify
Application.DisplayAlerts = True
Cells(iRow, iCol).Select
End Sub
please could you helpe me to move sheet data in my workbook to spreedsheet control in a user form