Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

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.

Data Validation Drop Down

Automatically Resize the TextBox

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

  1. Right-click on the TextBox, and click Format Shape
  2. Click the Text Box category
  3. In the Autofit section, add a check mark to Resize Shape to Fit Text
  4. 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

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.

 

Last updated: September 17, 2013