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

range a1 with long text in it

range a1:h3 with truncated, justified text

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.

Sub MyJustify()
    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
                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
            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)
                Selection.Columns(1).Cells(i + 1).Value = sContents
                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.

Posted in Uncategorized

2 thoughts on “Justify

  1. 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

    Range(Cells(iRow, iCol), Cells(iEnd, iCol + iCols – 1)).Select

    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

    Cells(iRow, iCol).Select

    End Sub

  2. please could you helpe me to move sheet data in my workbook to spreedsheet control in a user form

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.