Excel VBA, Wrap Text On/Off

Jul 30, 2015 by azurous in Alignment

In this article I will explain how you can use VBA to modify the wrap text property of a cell or range. I will also explain how you can use VBA to check the current status of the wrap text property for a cell or range.

Jump To:

Basics, .WrapText
Example 1, Set Wrap Text Property
Example 2, Get Wrap Text Property

You can download the file and code related to this article here.

–

Contents

Basics, .WrapText:

The following code applies the wrap text formatting to cell A1:

Range("A1").WrapText = True

Before:

After:

The following code removes the wrap text formatting from cell A1:

Range("A1").WrapText = False

–

Example 1, Set Wrap Text Property:

In this example there is text in row 2. There are drop down lists in row 1 where the user selects whether to apply the wrap text formatting or not. For more information about drop down lists in Excel please see Excel VBA Drop Down Lists:

After selecting “No” from the drop down list the wrap text property of cell F2 is set off:

The Yes/No values in the drop down list are stored in sheet 2:

The program uses a Worksheet_Change event handler. The event handler executes whenever the user selects a new value from the drop down lists:

'executes when drop down list values change Private Sub worksheet_change(ByVal target As Range) Dim i As Integer For i = 1 To 6 If Cells(1, i) = "Yes" Then 'sets the text wrap property to true Range(Cells(2, i), Cells(2, i)).WrapText = True Else 'sets the text wrap property to false Range(Cells(2, i), Cells(2, i)).WrapText = False End If Next i End Sub

–

Example 2, Get Wrap Text Property:

In the following example similar to Example 1, there are text values in row 2. When the user presses the run button the program searches the cells in row 2. The cells above the cells with the wrap text formatting will be colored green:

As you can see in the figure below the user turned off the wrap text property from cell A2 and therefore removing the green color from cell A1. Also the wrap text property of cell B2 and D2 were set on, therefore coloring the cells B1 and D1 to green:

This is the code used in the event handler for the Run button:

'event handler for the run button Private Sub btnRun_Click() Dim i As Integer 'loops through the cells in row 2 For i = 1 To 6 'checks if the wrap property is on If Range(Cells(2, i), Cells(2, i)).WrapText = True Then 'colors the cell green Range(Cells(1, i), Cells(1, i)).Interior.Color = 3394611 Else 'removes any previous color assigned to the cell Range(Cells(1, i), Cells(1, i)).Interior.Color = xlNone End If Next i End Sub

The If statement below checks the current wrap text property of the cell:

If Range(Cells(2, i), Cells(2, i)).WrapText = True Then ... End If

The line below colores the cell green. The number 3394611 is a color code. This was obtained using the macro recorder. For more information about the macro recorder please see Excel VBA Formatting Cells and Ranges Using the Macro Recorder:

Range(Cells(1, i), Cells(1, i)).Interior.Color = 3394611

The line below removes all previous coloring applied to the cell:

Range(Cells(1, i), Cells(1, i)).Interior.Color = xlNone

You can download the file and code related to this article here.