Excel VBA, Shrink To Fit On/Off

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

Jump To:

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


Contents

Basics, .ShrinkToFit:

The following code applies the shrink to fit formatting to cell A1:

Range("A1").ShrinkToFit = True

Before:

Text Wrap Before Excel VBA

After:

Text Shrink to Fit After Excel VBA

The following code removes the shrink to fit formatting from cell A1:

Range("A1").ShrinkToFit = False


Example 1, Set Shrink to Fit 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 shrink to fit formatting or not. For more information about drop down lists in Excel please see Excel VBA Drop Down Lists:

Excel VBA, Shrink to Fit
After selecting “No” from the drop down list the shrink to fit property of cell F2 is set off:

Excel VBA, Shrink to Fit 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 shrink to fit property to true
        Range(Cells(2, i), Cells(2, i)).ShrinkToFit = True
    Else
        'sets the shrink to fit property to false
        Range(Cells(2, i), Cells(2, i)).ShrinkToFit = 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 shrink to fit formatting will be colored green:

Excel VBA Get Shrink to Fit
As you can see in the figure below the user turned off the shrink to fit property from cell A2 and therefore removing the green color from cell A1. Also the shrink to fit property of cell C2 was turned on, therefore coloring the cell C1 green:

Excel VBA Get Shrink to Fit 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 shrink to fit property is on
    If Range(Cells(2, i), Cells(2, i)).ShrinkToFit = 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 shrink to fit property of the cell:

If Range(Cells(2, i), Cells(2, i)).ShrinkToFit = 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 *