Lock text box at 3rd paragraph?

[Ed]

I have a macro which inserts a text box and then pastes an Excel table
inside. The Excel table is generally too big, so I Format Object down to a
convenient size. Unfortunately, pasting in the Excel automatically enlarges
the text box (which is a good thing - I like the box to automatically adjust
to the size of the inserted object) and moves it from where the insertion
point was when the macro fired.

I have a landscape page with a single line header and some blank paragraphs
underneath. The insertion point is on the 3rd paragraph, leaving one blank
paragraph space between the header and the text box - supposedly. But when
all the formatting is over, I always have to move the text box back into
position. The code that inserts and formats the text box is below - what
can I change to lock the box centered on the page and the top at the 3rd
paragraph?

Ed

Sub InsertExcel()
'
' InsertExcel Macro
' Macro recorded 11/30/2004 by Authorized User
'
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 248.8, _
88.95, 128.15, 95.3).Select
Selection.ShapeRange.TextFrame.TextRange.Select
Selection.Collapse
With Selection.ShapeRange
.Select
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Transparency = 0#
.Line.Visible = msoFalse
.LockAspectRatio = msoFalse
.Height = 95.05
.Width = 128.15
.TextFrame.MarginLeft = 3.6
.TextFrame.MarginRight = 3.6
.TextFrame.MarginTop = 3.6
.TextFrame.MarginBottom = 3.6
.RelativeHorizontalPosition = _
wdRelativeHorizontalPositionColumn
.RelativeVerticalPosition = _
wdRelativeVerticalPositionPage
.Left = wdShapeCenter
.Top = InchesToPoints(1.1)
.LockAnchor = False
.WrapFormat.AllowOverlap = True
.WrapFormat.Side = wdWrapBoth
.WrapFormat.DistanceTop = InchesToPoints(0)
.WrapFormat.DistanceBottom = InchesToPoints(0)
.WrapFormat.DistanceLeft = InchesToPoints(0.13)
.WrapFormat.DistanceRight = InchesToPoints(0.13)
.WrapFormat.Type = wdWrapTopBottom
.TextFrame.TextRange.Select
End With

Selection.Collapse
Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject,
Placement _
:=wdInLine, DisplayAsIcon:=False
End Sub

 

[Helmut Weber]

Hi Ed,
read help on anchor property carfully.
I think the missing anchor is the problem.
Have a look at this exapmle.

With ActiveDocument.Shapes
.AddTextbox(msoTextOrientationHorizontal, _
10, 10, 100, 100, _
Anchor:=Selection.Range).Select
End With
With Selection
.ShapeRange.TextFrame.TextRange.Select
.Collapse
.Paragraphs(1).LineSpacingRule = wdLineSpaceSingle
.PasteSpecial _
Link:=False, DataType:=wdPasteOLEObject
End With

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[Ed]

Thank you, Helmut. That got me straight on the anchor. I did a bit more
exploring as well and figured out which properties set the actual position
of my box. The problem is that after I adjust the object inside the box, I
lose the original position settings, wherever the anchor is. So I put the
..Top and .Left settings into a modeless UserForm that pops up at the end of
the macro. Now I run the macro, adjust the object, and click the button to
set the box exactly where I want it.

I appreciate the boost.
Ed