VBA Excel - Word Wrap

I'm creating a small piece of VBA code with a specific formula, however it has a couple of if statements, one of which originates a double-line string (with vbNewLine) The issue is that I can't see the text.

So I wanted to word wrap it, but each time I 开发者_如何学编程set the ActiveCell.WrapText = True, nothing happens.

I checked with a message box. I set the WrapText to True, I return the property value with the MessageBox to confirm, and it's still False.

I've been told to use ActiveCell.Rows.AutoFit as well, but AutoFit does nothing if the text isn't wrapped.

Any idea what I might be doing wrong here?

---

try:

Sub WrapandFit()

    ActiveCell.WrapText = True
    ActiveCell.EntireRow.AutoFit

End Sub

It worked for me. Make sure that your screenupdating is also set to true.

---

For me, the code below worked. (only set to change header row, (change range))

ActiveSheet.Range("A1:R1").Select
With Selection
    .WrapText = True
End With

---

UDFs (procedures that use the keyword Function) only return values. They cannot change other parts of the Excel object model, like cell formatting. Only Subroutines (procedures that use the keyword Sub) can do that.

You need to have your cells formatted properly before you enter your UDF. Or you could use a worksheet change event sub to format them after the fact.

---

Turn off/On word wrap for whole sheet row can be done by VB code shown below: If the first row is set true, excel inherits that property for whole sheet, unless you specifically turned it off using another code.

MyWorkSheet.Rows.WrapText = True

To turn off wrapping property of a specific row:

MyWorkSheet.Rows(8).WrapText = False

---

I suspect that you are trying to wrap text in merged cells. If yes, you cannot simply call:

MyWorkSheet.Rows.WrapText = True

Instead, you have to simulate the wrapping operations. I found the code from http://blog.contextures.com/archives/2012/06/07/autofit-merged-cell-row-height/ helped me last year.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MergeWidth As Single
Dim cM As Range
Dim AutoFitRng As Range
Dim CWidth As Double
Dim NewRowHt As Double
Dim str01 As String
str01 = "OrderNote"

  If Not Intersect(Target, Range(str01)) Is Nothing Then
    Application.ScreenUpdating = False
    On Error Resume Next
    Set AutoFitRng = Range(Range(str01).MergeArea.Address)

    With AutoFitRng
      .MergeCells = False
      CWidth = .Cells(1).ColumnWidth
      MergeWidth = 0
      For Each cM In AutoFitRng
          cM.WrapText = True
          MergeWidth = cM.ColumnWidth + MergeWidth
      Next
      'small adjustment to temporary width
      MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
      .Cells(1).ColumnWidth = MergeWidth
      .EntireRow.AutoFit
      NewRowHt = .RowHeight
      .Cells(1).ColumnWidth = CWidth
      .MergeCells = True
      .RowHeight = NewRowHt
    End With
    Application.ScreenUpdating = True
  End If

End Sub

---

This may not be exactly what the OP had in mind but I figured I'd share my VBA Word Wrap function since I couldn't find anything on the web to do what I wanted.

This function insert CR+LF's into the string in order to wrap it, so the word wrap is maintained if the text is copied to another application, text-based or otherwise.

Function wrapText(strIn As String, Optional maxLen As Long = 110) As String
    Dim p As Long: wrapText = strIn
    Do
        p = InStrRev(wrapText, " ", p + maxLen) - 1
        wrapText = Left(wrapText,p) & vbCrLf & Right(wrapText, Len(wrapText)-p-1)
        Debug.Print Mid(Replace(wrapText, vbCrLf, "||"), p - 20)
        'Stop
    Loop While p + maxLen < Len(wrapText)
End Function

It defaults to maximum width of 115 characters but can optionally be changed to anything. It only breaks on spaces (the last one that appears on/before position #115), and it only inserts CR + LF's (with the constant vbCrLf), but they can be adapted as required.

As an example of application, I was building complex SQL queries in Excel and wanted to copy the SQL over to the server app neat & tidy, instead of one giant line.