Home > Format > Text Box

Excel Text Box Formatting

Help users by showing instructions in a worksheet Text Box. Manually change the text box settings, or use VBA to format the text box and resize it quickly

Show Instructions in Text Box

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 Text Box, by dragging the handles, to ensure that all the text is visible.

Automatically Resize Text Box

To see the steps for adding a Text Box, and resizing it automatically, you can watch this short Excel video tutorial.

There are written instructions below the video, and a full transcript at the end of the page.

Video Timeline

• 00:00 Introduction
• 00:10 Insert a Text Box
• 00:23 Text Box Too Small
• 00:32 Change Resize Setting
• 00:58 Wrap Text Not Working
• 01:08 Turn on Wrap Text
• 01:40 Test the Text Box
• 02:02 Adjust Height
• 02:20 Use a Macro
• 02:50 Test the Macro

Add Text Box to Worksheet

To insert a Text Box on an Excel worksheet, follow these steps

• On the Excel Ribbon's Insert tab, click Text Box
• On the worksheet, drag the pointer, to make a text box of the size that you want
• Type a message in the text box
  • OR
  • Copy text from another location, and paste that text into the Text Box

Change Text Box Size

After you add a Text box to the worksheet, you can adjust its size, if needed. For example, if you add more text to the existing message, the text box might be too small to show all of the text.

To manually adjust the text box size, follow these steps:

• Click on the text box border, to select the text box
• Point to one of the handles on the text box border
• When the pointer shape changes to a two-headed arrow, drag the handle, in or out, to reach the desired size
  • In - Drag in, toward the centre of the text box, to make it smaller
  • Out - Drag out, away from the centre of the text box, to make it larger

Note: If you drag on a corner handle, the size will adjust in two directions - height and width

How to Resize TextBox Automatically

Instead of manually adjusting the Text Box 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.

Macro - Resize Shape to Fit - ON

If your Excel 2010 (or later version) 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.

Add Code to Your Workbook

Copy the VBA code below, and paste it into a regular module window in your workbook.

• Tip: There are videos and written steps at this link: Excel VBA -- Adding Code to a Workbook

Warning: After you add the code to your workbook, be sure to save the file again, in macro-enabled workbook format. Otherwise, Excel will delete all the code from the workbook, when you close it.

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

Macro - 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 (or later version) workbook.

This line of code is different from the code above, and turns off the AutoSize setting:

• .AutoSize = msoAutoSizeNone

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

Video Transcript

Here is the full transcript for the video at the top of this page.

Introduction

If you're creating a workbook for other people to use in Excel, you can add a text box with instructions to help them get started

Insert a Text Box

• On the Insert tab, click Text Box
• Drag to make a text box of a specific size
• (Paste text message into Text Box)

If you adjust the size, the text wraps automatically, to show a little bit less on each line, and fill that text box

Text Box Too Small

But if the text box gets too small, some of the text might be chopped off, and they might not see some crucial information, that is lower down in that text box

If I want to make sure that all the text shows up, I can set the text box to resize automatically

Change Resize Setting

With the text box selected:

• Right click on the border
• Click Format Shape
• I'll click Text Box
• And there's a checkbox here to Autofit, and it will resize to fit the text
• Click that, and click Close

Everything is visible now, and if I add more text, the box would expand automatically to fit

Wrap Text Not Working

But we've lost the Wrap Text

I could add some line breaks of my own, but that's the only workaround, using Excel 2007

Turn on Wrap Text

If you're using Excel 2010 (or later), there's a wrap text feature that's been added

Here we have Excel 2010 open, and a text box sitting on the worksheet. We can see that it's chopped off at the end. Some of the text isn't visible

• Right-click on the text box
• And click Format Shape
• And again we'll go to Text Box
• It looks very similar to what we saw in 2007
• So there's Resize Shape, but now we can also set it to wrap the text within that shape
• I'm going to click Resize Shape
• And Close

And it didn't widen that text box, so that will all fit across

Test the Text Box

If I drag a corner handle, it keeps the width that I adjusted to, but makes it tall enough to fit all the text

If I change the font size, it gets taller, to fit everything

So you can adjust with the width handle, or the corner handle

Adjust Height

However, if you drag the bottom or top handles, that turns off the Auto Sizing

So now that we've done that:

• If we go back into Format Shape
• on the Text Box
• we can see that was automatically turned off, because we dragged either the bottom or the top handle

Use a Macro

If you have a workbook with lots of Text Boxes, that you want to change these settings on, you could use a macro.

I've created one that we can see here in Visual Basic

It has a variable for shape, and worksheet, and then it:

• runs through each worksheet in the active workbook worksheets
• for each shape on that worksheet
• it looks at the text frame
• sets the Auto Size to shape to Fit Text
• and turns the Word Wrap to true

Test the Macro

I'll just click in here, and click the Run button

Now each text box, or each shape that has text in it, like this rectangle, would have that setting changed

Visit the Contextures website, and there is a sample file that you can download, with the code to change the text box auto sizing

Get the Sample 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.

Excel Tutorials - VBA

Excel VBA ComboBox Lists

Excel VBA Worksheet Macro Buttons

Record and Test an Excel Macro

Excel VBA -- Adding Code to a Workbook