About Text Frame Overflows in Office 2010
Programmatically Working with Shapes in Office 2010: Learn how text overflow is handled in a text frame in Microsoft Excel 2010.
Last modified: April 22, 2011
Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | VBA | Word 2010
Published: April 2011
Provided by: Frank Rice, Microsoft Corporation
All objects positioned on a page in a Microsoft Excel 2010 workbook have an invisible bounding box or frame around the object. Text frames hold text. In this topic, you programmatically add a simple shape and text frame to a worksheet and then demonstrate different ways of working with text overflow. To complete these tasks, you must do the following:
In this task, you add programming code to the Visual Basic Editor that adds a shape to the worksheet. The code then demonstrates working with various text frame properties.
To add code to the Visual Basic Editor
Open a new Excel 2010 workbook.
Press Alt+F11 to open the Visual Basic Editor.
In the Project pane, double-click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code in the window that appears.
Sub TestTextFrame() Dim tf As TextFrame Dim shp As Shape Set shp = Shapes.AddShape(msoShapeRectangle, 0, 0, 90, 30) shp.TextFrame.Characters.Font.Color = vbBlack shp.Fill.BackColor.ObjectThemeColor = msoThemeColorBackground1 Set tf = shp.TextFrame ' Horizontal overflow only has effect if the WordWrap ' property is set to msoFalse: shp.TextFrame2.WordWrap = msoFalse tf.Characters.Text = "How will this text lay out in the text frame?" ' This is the default value: tf.HorizontalOverflow = xlOartHorizontalOverflowClip ' Cause the text that doesn't fit to overflow the shape: tf.HorizontalOverflow = xlOartHorizontalOverflowOverflow ' Vertical overflow only has effect if the WordWrap property ' is set to msoTrue, and the AutoSize property is set to msoAutoSizeNone. shp.TextFrame2.WordWrap = msoTrue shp.TextFrame2.AutoSize = msoAutoSizeNone ' This is the default value: tf.VerticalOverflow = xlOartVerticalOverflowClip ' Display an ellipsis for overflow: tf.VerticalOverflow = xlOartVerticalOverflowEllipsis ' Simply allow the text to overflow the shape: tf.VerticalOverflow = xlOartVerticalOverflowOverflow End Sub
In the following steps, you step through the code. A shape and text frame are added to the worksheet and then various ways of handling text overflows is demonstrated. The best way to see the code in action is to place the Visual Basic Editor window and the Excel window side-by-side.
To step through the code
Drag the Visual Basic Editor window to the right side of your screen.
Next, drag the Excel window to the left side of your screen and adjust both windows until you can see them both.
Now, place the cursor in the TestTextFrame module and then press F8 to step through the code line-by-line.