Export (0) Print
Expand All

About Text Frame Overflows in Office 2010

Office 2010

Office Quick Note banner

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

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

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

  1. Open a new Excel 2010 workbook.

  2. Press Alt+F11 to open the Visual Basic Editor.

  3. In the Project pane, double-click Sheet1.

  4. 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

  1. Drag the Visual Basic Editor window to the right side of your screen.

  2. Next, drag the Excel window to the left side of your screen and adjust both windows until you can see them both.

  3. Now, place the cursor in the TestTextFrame module and then press F8 to step through the code line-by-line.

Show:
© 2015 Microsoft