Calculate vertical midpoint of a print area in a sheet

[quartz]

Is it even possible to calculate the midpoint of a sheet taking into account margins, row heights, font sizes, and anything else that could affect it

I am trying to place a graphic (a text box) at the left edge (horizontal) and center of the page (vertical) using VBA. The sheets I have to work with use all different font sizes, margins, row heights, etc. It seems impossible to do.

Have I found a limit in VBA

If you can accurately calculate this, please, pleasE, pleaSE, pleASE, plEASE, pLEASE, PLEASE post your code so I can solve this mystery.

Thanks much in advance for your assistance

 

[Tom Ogilvy]

Seems you could find the pagebreak, then add up the heights of the rows in
the page, then go halfway.

--
Regards,
Tom Ogilvy

Is it even possible to calculate the midpoint of a sheet taking into

account margins, row heights, font sizes, and anything else that could
affect it?

I am trying to place a graphic (a text box) at the left edge (horizontal)

and center of the page (vertical) using VBA. The sheets I have to work with
use all different font sizes, margins, row heights, etc. It seems impossible
to do.

Have I found a limit in VBA?

If you can accurately calculate this, please, pleasE, pleaSE, pleASE,

plEASE, pLEASE, PLEASE post your code so I can solve this mystery.

 

[BrianB]

This will work if :-
1. There is only 1 page.
2. The Print Range is set (or you can supply another range)

'----------------------------------------------------
Sub test()
'- requires that Print area is set
Dim TotalRowHeight As Long
Dim PictureHeight As Long
'-- calculate height
TotalRowHeight = 0
For Each RW In ActiveSheet.Range("Print_Area").Rows
TotalRowHeight = TotalRowHeight + RW.RowHeight
Next
'- change picture position
PictureHeight = ActiveSheet.OLEObjects("Object 1").Height
ActiveSheet.OLEObjects("Object 1").Top = (TotalRowHeight / 2)
(PictureHeight / 2)
End Sub
'--------------------------------------------