Excel Macro: Turning Off the Wrap Text on Active Workbook

This Excel VBA macro turns off the wrap text check-box on an active workbook.

Macro Example

Sub TurningOffWrapText_ActiveBook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Cells.WrapText = False
  Next sh

End Sub

Description

Variable Declaration

The 3rd line declares an object variable called sh which will refer to a Worksheet object.

Sub...

  Dim sh As Worksheet

The Worksheet object represents a worksheet in an Excel workbook.

obj-Excel-Worksheet

Starts Looping

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets

The 5th line starts to loop through all the elements (=Worksheet) of a collection (=Worksheets).

[Application.]Worksheets

The Application.Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in the workbook.

obj-Excel-Application-Worksheets

The Worksheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Worksheets

Turns Off the Wrap Text

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Cells.WrapText = False

The 6th line turns off the wrap text.

Worksheet.Cells

    sh.Cells...

The Worksheet.Cells property returns the Range object represents ALL ranges on a sheet.

obj-Excel-Worksheet-Cells

Range.WrapText

    sh.Cells.WrapText...

The Cells.WrapText (Range.WrapText property) returns or sets a Variant value that indicates if Excel wraps the text in a cell.

obj-Excel-Range-WrapText

This property returns True if the text is wrapped, False if the text isn’t wrapped, or Null if the range contains some cells that wrap text and other cells that don’t.

    sh.Cells.WrapText = False

Loops Back

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Cells.WrapText = False
  Next sh

The 7th line loops back to get the next worksheet.

After every worksheet is evaluated, this macro ends.

Sub TurningOffWrapText_ActiveBook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Cells.WrapText = False
  Next sh

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-off-wrap-text.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007