"Allow text to overflow shape" in excel 2010 vba

Question

Could I please be pointed to how I could target the "Allow Text to Overflow Shape" of a Textbox created via VBA using AddShape? I have looked through the object reference textframe/textframe2 with no luck.

This can be achieved without vba[but I have lots of textboxes] by:

right click [on the textbox shape] >> format shape >> textbox >> Allow Text to overflow shape [tick box under the "Autofit" group]

I have also done macro recording to no avail

Suggestions much appreciated

Answer 1

The setting you refer to seem to affect two properties in the Excel object model, TextFrame.HorizontalOverflow and TextFrame.VerticalOverflow.

VerticalOverflow can be set to:

• xlOartVerticalOverflowClip - no overflow
• xlOartVerticalOverflowEllipsis - overflow is marked with three dots (...)
• xlOartVerticalOverflowOverflow - allows overflow

HorizontalOverflow can only be set to:

• xlOartHorizontalOverflowClip - no overflow
• xlOartHorizontalOverflowOverflow - allows overflow

Working example (Excel 2010):

Sheet1.Shapes(1).TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow

EDIT:

Also see the documentation at MSDN, where it says that the VerticalOverflow setting only has an effect when the AutoSize property is False, and that the HorizontalOverflow setting only has an effect when the TextFrame2.WordWrap property is msoFalse (0).