Calculate vertical midpoint of a print area in a sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Seems you could find the pagebreak, then add up the heights of the rows in
the page, then go halfway.

--
Regards,
Tom Ogilvy

quartz said:
Is it even possible to calculate the midpoint of a sheet taking into
Click to expand...
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)
Click to expand...
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,
Click to expand...
plEASE, pLEASE, PLEASE post your code so I can solve this mystery.
 
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
'--------------------------------------------
 
Back
Top