ExcelBanter - recalculate page breaks after setting Zoom in pagesetup

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - recalculate page breaks after setting Zoom in pagesetup (https://www.excelbanter.com/excel-discussion-misc-queries/147373-recalculate-page-breaks-after-setting-zoom-pagesetup.html)

recalculate page breaks after setting Zoom in pagesetup

Cross posted since I have gotten no responce in
microsoft.public.excel.programming for 3 days

Ok I've seen the 1,000s of people trying to program page setup
options
and having a bad time. So let me add more problems.

I was using Excel 2000. I now have 2007. I had a workaround for
this
bug in 2000/2003 but I can't seem to get around it in 2007 (and of
course it isn't fixed)

Here is what I'm trying to do:
Set the page to Landscape, 1 page wide
Then add some intelligent page breaks by looking for the automatic
page break and putting a manual break above it where the data
recently
changed.

The problem is that the automatic page break is not recalculated
after
setting the zoom factor programatically.

if I do:
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 65
End With

the first page break is on row 33

In 2000/2003 I could :
SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
True

This is a crappy way to get around it but after the sendkeys the
first
page break would not be row 53

I cannot get a key stroke method to the page setup dialog box in
Excel2007 to work from sendkeys (I can do it manually but that
doesn't
help)

Anybody know how to get Excel to recalculate the page breaks?
Obviously something is being called from teh page setup dialog box
that I can't find directly.

Thanks for any help.
Pete

recalculate page breaks after setting Zoom in pagesetup

recalculate page breaks after setting Zoom in pagesetup

Jim,
That is incredible. It works but I have no idea why. It is such an
obvious solution I don't know why I didn't think of it (NOT)

So I looked up the doc on papersize and it sure doesn't say anything
(surprising isn't it) that would lead me to try this.

Thanks a lot. I have been fighting this problem in my spare time for
over a week

Pete

On Jun 21, 7:45 am, "Jim Rech" wrote:
Does this help?:

With Sheet1.PageSetup
.Zoom = 65
.PaperSize = .PaperSize
End With

--
Jim"Par" wrote in message

oups.com...
| Cross posted since I have gotten no responce in
| microsoft.public.excel.programming for 3 days
|
| Ok I've seen the 1,000s of people trying to program page setup
| options
| and having a bad time. So let me add more problems.
|
| I was using Excel 2000. I now have 2007. I had a workaround for
| this
| bug in 2000/2003 but I can't seem to get around it in 2007 (and of
| course it isn't fixed)
|
|
| Here is what I'm trying to do:
| Set the page to Landscape, 1 page wide
| Then add some intelligent page breaks by looking for the automatic
| page break and putting a manual break above it where the data
| recently
| changed.
|
|
| The problem is that the automatic page break is not recalculated
| after
| setting the zoom factor programatically.
|
|
| if I do:
| With ActiveSheet.PageSetup
| .Orientation = xlLandscape
| .Zoom = 65
| End With
|
|
| the first page break is on row 33
|
|
| In 2000/2003 I could :
| SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
| True
|
|
| This is a crappy way to get around it but after the sendkeys the
| first
| page break would not be row 53
|
|
| I cannot get a key stroke method to the page setup dialog box in
| Excel2007 to work from sendkeys (I can do it manually but that
| doesn't
| help)
|
|
| Anybody know how to get Excel to recalculate the page breaks?
| Obviously something is being called from teh page setup dialog box
| that I can't find directly.
|
|
| Thanks for any help.
| Pete
|

recalculate page breaks after setting Zoom in pagesetup