Excel VBA, Wrap Text On/Off

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:

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:

Text Wrap Before Excel VBA

After:

Text Wrap After Excel VBA

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:

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

Excel VBA, Wrap Text No
The Yes/No values in the drop down list are stored in sheet 2:

Excel  VBA, YesNo
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:

Excel VBA Get Wrap Text
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:

Excel VBA Get Wrap Text After Changes
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.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *