Geek Answers Handbook

VBA Excel - Word Wrap

I am creating a small piece of VBA code with a specific formula, however there are several if , one of which takes a line with two lines (with vbNewLine ) The problem is that I cannot see the text.

So, I wanted the words to wrap it, but every time I set ActiveCell.WrapText = True , nothing happens.

I checked using the message box. I set the WrapText parameter to True, I am returning the property value with a MessageBox to confirm, and it is still False.

I was told to use ActiveCell.Rows.AutoFit , but AutoFit does nothing if the text is not wrapped.

Any idea what I can do wrong here?

+8
source share
6 answers

to try:

 Sub WrapandFit() ActiveCell.WrapText = True ActiveCell.EntireRow.AutoFit End Sub

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

+15
source

For me, the code below worked. (only for changing the title bar, (range of change))

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

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

Before you enter UDF, you must format your cells correctly. Or you can use the sub-account of the change in sheet time to format them after the fact.

+2
source

Turning words off / on for the entire line of the sheet can be done using the VB code shown below: If the first line is set to true, excel inherits this property for the whole sheet, unless you disabled it with another code.

 MyWorkSheet.Rows.WrapText = True

To disable the wrapping property of a specific string:

 MyWorkSheet.Rows(8).WrapText = False
+1
source

I suspect you are trying to wrap text in merged cells. If so, you cannot just call:

 MyWorkSheet.Rows.WrapText = True

Instead, you need to model wrapping operations. I found the code from http://blog.contextures.com/archives/2012/06/07/autofit-merged-cell-row-height/ that 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
+1
source

Perhaps this is not exactly what the OP had in mind, but I decided that I would share my VBA word wrap function, since I could not find anything on the Internet that could do what I wanted.

This function inserts CR + LF in a line to transfer it , so word wrap is preserved if the text is copied to another application, text 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

By default, the maximum width is 115 characters, but you can change it to anything if you wish. It breaks only into spaces (the last that appears in / before position # 115) and inserts only CR + LF (with the constant vbCrLf ), but they can be adapted as needed.

As an example of an application, I built complex SQL queries in Excel and wanted to carefully and accurately copy SQL to a server application instead of one giant line.

-1
source

More articles:


All Articles