Contextures

Excel TextBox Formatting

Help users by showing instructions in a TextBox. Use VBA to format the text box and resize it

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. go to top

adjust the size of the TextBox

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)

Resize Shape to Fit Text

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

TextBox will now adjust its size automatically

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

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. go to top

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. go to top

Note: Textboxes, and other shapes with text, such as Rectangles, will also be affected. go to top

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. go to top

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 xlsm format, and contains macros, so enable macros to test the code. go to top

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

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: September 13, 2016 7:48 PM
Contextures RSS Feed