Auto Resize Excel Text Boxes

Auto Resize Excel Text Boxes

If other people will be using the Excel files that you build, it might help them if you add some instructions in a Text Box. Here are a couple of tips for setting that up.

Adjust the Size

After you add the text, drag the handles to adjust the Text Box size, so all the instructions are visible.

drag the handles to adjust the Text Box size
drag the handles to adjust the Text Box size

If you plan to edit the text occasionally, or change the text formatting, you can set the Text Box to resize automatically.

  1. Right-click on the Text Box, and click Format Shape,
  2. Then, click the Text Box category, and add a check mark to Resize Shape to Fit Text.

textboxresize00

In Excel 2007 and later, the Text Box loses its Wrap Text setting, if you turn on the auto resize. Instead of a narrow text box, you’ll often end up with a really wide one.

Tip: Add a few manual line breaks, if you want the Text Box to be narrower.

textboxresize00b

Excel 2010 Auto Resize With Wrap Text

Thanks to Bob Ryan, of Simply Learning Excel, who let me know that the Text Box resize feature has improved in Excel 2010.

Now, when you open the Format Shape window, there is also a Wrap Text in Shape checkbox.

textboxresize01

After you change the Resize setting, the Text Box will get taller, to fit all the text, instead of getting wider.

textboxresize05

Adjust the Text Box

With the Auto Resize setting turned on, you can drag the side or corner handles to change the size of the text box.

The text box will adjust to the width that you have set, and automatically changes the height, so all the text is visible.

textboxresize02

Warning: If you resize the text box with the top or bottom handle, the Resize to Fit setting is automatically turned off.

textboxresize04

Text Box Resize Macro

If you’re using Excel 2010, and have lots of text boxes in a workbook, you can use a macro to change the Resize to Fit setting.

You can see the Text Resize code, and download the sample workbook, on the Contextures website: Excel TextBox Formatting

textboxresize06

Watch the Text Box Resize Video

To see the steps for inserting a text box, and formatting it in Excel 2007 or later, you can watch this short Excel video.

_________________

0 thoughts on “Auto Resize Excel Text Boxes”

  1. Great post. I was going bonkers recently, trying to figure this one out. I’m working with Excel 2007, and I couldn’t figure out how to get the text box to fit to the text, adjusting only the height. It’s good to at least see that I wasn’t missing something. I’ll have to look into a formulaic solution to adding line breaks when needed. Thanks!

  2. Helpful presentation. If you might resize column widths, one additional TextBox setting that helps is the Move but don’t size with cells.
    In VBA use: If sh.Type = msoTextBox Then sh.Placement = xlMove

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.