Excel TextBox Formatting

Search Contextures Sites

Excel TextBox Formatting
Show Instructions in a Textbox
Automatically Resize the TextBox
VBA - Resize Shape to Fit - ON
VBA - Resize Shape to Fit - OFF
VBA - Resize Shape to Fit - Textbox
Download the Sample TextBox Resize File
Watch the TextBox Resize Video

Download the zipped sample file: TextBox Resize VBA

Thanks to Bob Ryan, of Simply Learning Excel, for suggesting this tutorial.
Show Instructions in a TextBox

If you create a workbook that other people will use, you can help them get started, by showing instructions in a TextBox.

You can adjust the size of the TextBox, by dragging the handles, to ensure that all the text is visible.

Automatically Resize the TextBox

Instead of manually adjusting the TextBox size, you can change a setting, to have it resize automatically.

Right-click on the TextBox, and click Format Shape

Click the Text Box category

In the Autofit section, add a check mark to Resize Shape to Fit Text

Also check the Wrap Text in Shape setting (this setting is not available in Excel 2007 and earlier versions)

The TextBox will now adjust its size automatically, if you add or remove text, or change the font size.

Note: If you adjust the TextBox by dragging the top or bottom handle, the Resize Shape to Fit Text setting is automatically turned off.

VBA - Resize Shape to Fit - ON

If your Excel 2010 workbook contains several shapes, you can use this macro to turn ON the Resize Shape to Fit Text and Word Wrap setting for all of them.

Note: Textboxes, and other shapes with text, such as Rectangles, will also be affected.
Sub TextBoxResizeAll()
Dim sh As Shape
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    For Each sh In ws.Shapes
        With sh.TextFrame2
            .AutoSize = msoAutoSizeShapeToFitText
            .WordWrap = True
        End With
    Next sh
Next ws

End Sub
VBA - Resize Shape to Fit - OFF

You can use this macro to turn OFF the Resize Shape to Fit Text setting for all shapes in the active Excel 2010 workbook.

Note: Textboxes, and other shapes with text, such as Rectangles, will also be affected.
Sub TextBoxResizeOFF()
Dim sh As Shape
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    For Each sh In ws.Shapes
        With sh.TextFrame2
            .AutoSize = msoAutoSizeNone
            .WordWrap = True
        End With
    Next sh
Next ws

End Sub
VBA Resize Shape to Fit - TextBox

You can use this macro to turn OFF the Resize Shape to Fit Text setting for all TextBoxes in the active Excel 2010 workbook.
Sub TextBoxResizeTB()
Dim sh As Shape
Dim ws As Worksheet
Dim lType As Long
lType = 17 'TextBox

For Each ws In ActiveWorkbook.Worksheets
    For Each sh In ws.Shapes
        If sh.Type = lType Then
            With sh.TextFrame2
                .AutoSize = msoAutoSizeShapeToFitText
                .WordWrap = True
            End With
        End If
    Next sh
Next ws

End Sub
Download the Sample TextBox Resize File

To experiment with this technique, you can download the zipped sample file: TextBox Resize VBA The file is in Excel 2010 format, and contains macros, so enable macros to test the code.

Watch the TextBox Resize Video

To see the steps for adding a TextBox, and resizing it automatically, you can watch this short Excel video tutorial.
Excel Tutorials - VBA

Excel VBA ComboBox Lists
Excel VBA Worksheet Macro Buttons
Excel VBA - Getting Started
Excel VBA -- Adding Code to a Workbook

Home Excel Tips Excel Files The Excel Store Blog Contact

Privacy Policy

Contextures Inc., Copyright �2012
All rights reserved.

Debra Dalgleish

Last updated: August 21, 2012

	Show Instructions in a Textbox Automatically Resize the TextBox VBA - Resize Shape to Fit - ON VBA - Resize Shape to Fit - OFF VBA - Resize Shape to Fit - Textbox Download the Sample TextBox Resize File Watch the TextBox Resize Video Download the zipped sample file: TextBox Resize VBA Thanks to Bob Ryan, of Simply Learning Excel, for suggesting this tutorial.

	Show Instructions in a TextBox If you create a workbook that other people will use, you can help them get started, by showing instructions in a TextBox. You can adjust the size of the TextBox, by dragging the handles, to ensure that all the text is visible. Automatically Resize the TextBox Instead of manually adjusting the TextBox size, you can change a setting, to have it resize automatically. Right-click on the TextBox, and click Format Shape Click the Text Box category In the Autofit section, add a check mark to Resize Shape to Fit Text Also check the Wrap Text in Shape setting (this setting is not available in Excel 2007 and earlier versions) The TextBox will now adjust its size automatically, if you add or remove text, or change the font size. Note: If you adjust the TextBox by dragging the top or bottom handle, the Resize Shape to Fit Text setting is automatically turned off. VBA - Resize Shape to Fit - ON If your Excel 2010 workbook contains several shapes, you can use this macro to turn ON the Resize Shape to Fit Text and Word Wrap setting for all of them. Note: Textboxes, and other shapes with text, such as Rectangles, will also be affected. Sub TextBoxResizeAll() Dim sh As Shape Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets For Each sh In ws.Shapes With sh.TextFrame2 .AutoSize = msoAutoSizeShapeToFitText .WordWrap = True End With Next sh Next ws End Sub VBA - Resize Shape to Fit - OFF You can use this macro to turn OFF the Resize Shape to Fit Text setting for all shapes in the active Excel 2010 workbook. Note: Textboxes, and other shapes with text, such as Rectangles, will also be affected. Sub TextBoxResizeOFF() Dim sh As Shape Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets For Each sh In ws.Shapes With sh.TextFrame2 .AutoSize = msoAutoSizeNone .WordWrap = True End With Next sh Next ws End Sub VBA Resize Shape to Fit - TextBox You can use this macro to turn OFF the Resize Shape to Fit Text setting for all TextBoxes in the active Excel 2010 workbook. Sub TextBoxResizeTB() Dim sh As Shape Dim ws As Worksheet Dim lType As Long lType = 17 'TextBox For Each ws In ActiveWorkbook.Worksheets For Each sh In ws.Shapes If sh.Type = lType Then With sh.TextFrame2 .AutoSize = msoAutoSizeShapeToFitText .WordWrap = True End With End If Next sh Next ws End Sub Download the Sample TextBox Resize File To experiment with this technique, you can download the zipped sample file: TextBox Resize VBA The file is in Excel 2010 format, and contains macros, so enable macros to test the code. Watch the TextBox Resize Video To see the steps for adding a TextBox, and resizing it automatically, you can watch this short Excel video tutorial.