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
If you create a workbook that other people will use, you can help them get started, by showing instructions in a TextBox.
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.
Add Text Box to Worksheet
To insert a Text Box on an Excel worksheet, follow these steps
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:
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.
The TextBox will now adjust its size automatically, if you add or remove text, or change the font size.
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.
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
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:
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
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
Here is the full transcript for the video at the top of this page.
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
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:
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
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
However, if you drag the bottom or top handles, that turns off the Auto Sizing
So now that we've done that:
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:
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
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
Last updated: September 25, 2022 3:44 PM