How can I check to see if there is a vertical break?

[Guest]

Hi - I posted a question earlier today but didn't get a response - I think my
earlier question was probably not clear enough.

What I am doing is messing with the print zoom properties of the pagesetup
and after I mess with the zoom properties then I check to see how many
VPageBreaks there are - if there is a VPageBreak (ie
ActiveSheet.VPageBreaks.Count >0) then I change the zoom back down a notch
and check it again -- I know, messing with PageSetup is very slow, but when
I use the PagesWide=1 and PagesLong set to false then sometimes the print job
is not maximized as much as it could be - sometimes the 3-15% extra zoom I
can get makes all the difference.

The problem I am having is that when I change the zoom properties the
VPageBreak.Count is not updated with the new pagebreak count. If I am
stepping through the code and manually change something in the code
(unrelated something) then it will pull a new updated VPageBreak.Count, but
when it runs it doesn't seem to refresh the count.

I have been able to do some loops in the past where I assign a variable to
hold the vpagebreak.count and it will sometimes update with a new count, but
not always. I am wondering what I could do to force the count to update with
the latest value.

Any ideas? Even if it is a slow procedure, because it is much faster than
me manually doing it every time I print.

Thanks in advance --

Jim

 

[Jim Rech]

Someone recently had a similar question and I found that resetting the paper
size caused Excel to update the page break count. This demonstrates what I
mean:

Sub a()
Dim ZoomFactor As Integer
GoTo StartHere
Do
Sheet1.PageSetup.Zoom = ZoomFactor - 5
Sheet1.PageSetup.PaperSize = Sheet1.PageSetup.PaperSize
StartHere:
ZoomFactor = Sheet1.PageSetup.Zoom
Debug.Print Sheet1.VPageBreaks.Count
Debug.Print ZoomFactor
Loop Until Sheet1.VPageBreaks.Count = 1
End Sub


--
Jim
| Hi - I posted a question earlier today but didn't get a response - I think
my
| earlier question was probably not clear enough.
|
| What I am doing is messing with the print zoom properties of the pagesetup
| and after I mess with the zoom properties then I check to see how many
| VPageBreaks there are - if there is a VPageBreak (ie
| ActiveSheet.VPageBreaks.Count >0) then I change the zoom back down a notch
| and check it again -- I know, messing with PageSetup is very slow, but
when
| I use the PagesWide=1 and PagesLong set to false then sometimes the print
job
| is not maximized as much as it could be - sometimes the 3-15% extra zoom I
| can get makes all the difference.
|
| The problem I am having is that when I change the zoom properties the
| VPageBreak.Count is not updated with the new pagebreak count. If I am
| stepping through the code and manually change something in the code
| (unrelated something) then it will pull a new updated VPageBreak.Count,
but
| when it runs it doesn't seem to refresh the count.
|
| I have been able to do some loops in the past where I assign a variable to
| hold the vpagebreak.count and it will sometimes update with a new count,
but
| not always. I am wondering what I could do to force the count to update
with
| the latest value.
|
| Any ideas? Even if it is a slow procedure, because it is much faster than
| me manually doing it every time I print.
|
| Thanks in advance --
|
| Jim

 

[Guest]

Thank you for your response - I used your suggestion of changing the
papersize to its papersize - initially it didn't work, but then when I
coupled your response with changing the view to ActiveWindow.View =
xlPageBreakPreview then it worked!

Thank you for your resonse !!!! It helped a ton !!