Excel VBA, Shrink To Fit On/Off

Jul 21, 2015 by azurous in Alignment

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:

Basics, .ShrinkToFit
Example 1, Set Shrink to Fit Property
Example 2, Get Shrink to Fit Property

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:

After:

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:

After selecting “No” from the drop down list the shrink to fit 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 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:

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:

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.