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:
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.
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