return pageBreaks

JCIrish · 02-25-2006, 12:30 PM

Here is a simple code I've written that returns page break locations, but
only those page breaks that I've inserted manually. It won't return page
breaks that Excel inserts automatically. If I try to get these page breaks I
get the message, "subscript out of range. Can anybody tell me why? And how to
use this type of simple code to get automatic page breaks?

Sub pageBreakA1C1()

'will find only those page breaks that were manually inserted
'won't find those inserted automaticall by Excel

Dim myPageBreakA1C1
myPageBreakA1C1 =
ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, , xlR1C1)
Range("A3").Select
Selection = myPageBreakA1C1
Range("A4").Select
Range("A3").ClearContents

End Sub

Jim Cone · 02-25-2006, 01:10 PM

Enter your name in cell A150 and then try your code.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"JCIrish" <[email protected]> wrote in message...
Here is a simple code I've written that returns page break locations, but
only those page breaks that I've inserted manually. It won't return page
breaks that Excel inserts automatically. If I try to get these page breaks I
get the message, "subscript out of range. Can anybody tell me why? And how to
use this type of simple code to get automatic page breaks?

Sub pageBreakA1C1()
'will find only those page breaks that were manually inserted
'won't find those inserted automaticall by Excel
Dim myPageBreakA1C1
myPageBreakA1C1 =
ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, , xlR1C1)
Range("A3").Select
Selection = myPageBreakA1C1
Range("A4").Select
Range("A3").ClearContents
End Sub

NickHK · 02-27-2006, 12:20 AM

JCIrish,
Not sure what you are trying to achieve with the code but you will only be
to work with HPageBreaks(2), if it exists, hence the Subscript error.
To work with all Hbreaks:
Dim myHPageBreak As HPageBreak
For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks
With myHPageBreak
Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual)
Debug.Print "Location : " & .Location.Address(, , xlR1C1)
End With
Next
End Sub

"JCIrish" <[email protected]> wrote in message
news:[email protected]...
>
> Here is a simple code I've written that returns page break locations, but
> only those page breaks that I've inserted manually. It won't return page
> breaks that Excel inserts automatically. If I try to get these page breaks
I
> get the message, "subscript out of range. Can anybody tell me why? And how
to
> use this type of simple code to get automatic page breaks?
>
> Sub pageBreakA1C1()
>
> 'will find only those page breaks that were manually inserted
> 'won't find those inserted automaticall by Excel
>
> Dim myPageBreakA1C1
> myPageBreakA1C1 =
> ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, , xlR1C1)
> Range("A3").Select
> Selection = myPageBreakA1C1
> Range("A4").Select
> Range("A3").ClearContents
>
> End Sub
>

JCIrish · 02-27-2006, 12:30 PM

Hi, NickHK
Thanks for the very useful response. I'm new at this. The last two lines of
my code were put there simply to let me step through it several times,
clearing the results and changing the HPageBreaks() subscript to observe the
results. I just wanted to know where all the page breaks occur. I see the
logic of what you've written and I will try that. I've not run into the code,
"Debug.Print", so, not yet having tried it, I don't know what it does. Ditto
for the line,"Next." Can you tell me something about these lines?

Again, thanks a lot for giving a rookie a hand.

JCIrish

"NickHK" wrote:

> JCIrish,
> Not sure what you are trying to achieve with the code but you will only be
> to work with HPageBreaks(2), if it exists, hence the Subscript error.
> To work with all Hbreaks:
> Dim myHPageBreak As HPageBreak
> For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks
> With myHPageBreak
> Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual)
> Debug.Print "Location : " & .Location.Address(, , xlR1C1)
> End With
> Next
> End Sub
>
>
> "JCIrish" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Here is a simple code I've written that returns page break locations, but
> > only those page breaks that I've inserted manually. It won't return page
> > breaks that Excel inserts automatically. If I try to get these page breaks
> I
> > get the message, "subscript out of range. Can anybody tell me why? And how
> to
> > use this type of simple code to get automatic page breaks?
> >
> > Sub pageBreakA1C1()
> >
> > 'will find only those page breaks that were manually inserted
> > 'won't find those inserted automaticall by Excel
> >
> > Dim myPageBreakA1C1
> > myPageBreakA1C1 =
> > ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, , xlR1C1)
> > Range("A3").Select
> > Selection = myPageBreakA1C1
> > Range("A4").Select
> > Range("A3").ClearContents
> >
> > End Sub
> >
>
>
>

NickHK · 02-27-2006, 10:50 PM

JCIrish,
Debug (or Immediate) is one of the Windows you have available to see direct
output from your app, or test results.
If it is not visible, go to View>Immediate
Look there to see the result of any Debug.Print statements.

As for "Next", it marks the end of the "For Each..." block. Check the help
for loops.

NickHK

"JCIrish" <[email protected]> wrote in message
news:[email protected]...
> Hi, NickHK
> Thanks for the very useful response. I'm new at this. The last two lines
of
> my code were put there simply to let me step through it several times,
> clearing the results and changing the HPageBreaks() subscript to observe
the
> results. I just wanted to know where all the page breaks occur. I see the
> logic of what you've written and I will try that. I've not run into the
code,
> "Debug.Print", so, not yet having tried it, I don't know what it does.
Ditto
> for the line,"Next." Can you tell me something about these lines?
>
> Again, thanks a lot for giving a rookie a hand.
>
> JCIrish
>
> "NickHK" wrote:
>
> > JCIrish,
> > Not sure what you are trying to achieve with the code but you will only
be
> > to work with HPageBreaks(2), if it exists, hence the Subscript error.
> > To work with all Hbreaks:
> > Dim myHPageBreak As HPageBreak
> > For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks
> > With myHPageBreak
> > Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual)
> > Debug.Print "Location : " & .Location.Address(, , xlR1C1)
> > End With
> > Next
> > End Sub
> >
> >
> > "JCIrish" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > Here is a simple code I've written that returns page break locations,
but
> > > only those page breaks that I've inserted manually. It won't return
page
> > > breaks that Excel inserts automatically. If I try to get these page
breaks
> > I
> > > get the message, "subscript out of range. Can anybody tell me why? And
how
> > to
> > > use this type of simple code to get automatic page breaks?
> > >
> > > Sub pageBreakA1C1()
> > >
> > > 'will find only those page breaks that were manually inserted
> > > 'won't find those inserted automaticall by Excel
> > >
> > > Dim myPageBreakA1C1
> > > myPageBreakA1C1 =
> > > ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, ,
xlR1C1)
> > > Range("A3").Select
> > > Selection = myPageBreakA1C1
> > > Range("A4").Select
> > > Range("A3").ClearContents
> > >
> > > End Sub
> > >
> >
> >
> >

JCIrish · 02-28-2006, 04:15 PM

Thanks again, NickHk. Since I recently started with VBA, winging my way
through it, I bought a book to help the process. I got hung up on this
pageBreak problem, which is really tangential to my main concern, and your
post was very helpful. I'm grateful. (I still can't, however, return page
breaks that are automatic!)
Thanks,

JCIrish

"NickHK" wrote:

> JCIrish,
> Debug (or Immediate) is one of the Windows you have available to see direct
> output from your app, or test results.
> If it is not visible, go to View>Immediate
> Look there to see the result of any Debug.Print statements.
>
> As for "Next", it marks the end of the "For Each..." block. Check the help
> for loops.
>
> NickHK
>
> "JCIrish" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, NickHK
> > Thanks for the very useful response. I'm new at this. The last two lines
> of
> > my code were put there simply to let me step through it several times,
> > clearing the results and changing the HPageBreaks() subscript to observe
> the
> > results. I just wanted to know where all the page breaks occur. I see the
> > logic of what you've written and I will try that. I've not run into the
> code,
> > "Debug.Print", so, not yet having tried it, I don't know what it does.
> Ditto
> > for the line,"Next." Can you tell me something about these lines?
> >
> > Again, thanks a lot for giving a rookie a hand.
> >
> > JCIrish
> >
> > "NickHK" wrote:
> >
> > > JCIrish,
> > > Not sure what you are trying to achieve with the code but you will only
> be
> > > to work with HPageBreaks(2), if it exists, hence the Subscript error.
> > > To work with all Hbreaks:
> > > Dim myHPageBreak As HPageBreak
> > > For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks
> > > With myHPageBreak
> > > Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual)
> > > Debug.Print "Location : " & .Location.Address(, , xlR1C1)
> > > End With
> > > Next
> > > End Sub
> > >
> > >
> > > "JCIrish" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > Here is a simple code I've written that returns page break locations,
> but
> > > > only those page breaks that I've inserted manually. It won't return
> page
> > > > breaks that Excel inserts automatically. If I try to get these page
> breaks
> > > I
> > > > get the message, "subscript out of range. Can anybody tell me why? And
> how
> > > to
> > > > use this type of simple code to get automatic page breaks?
> > > >
> > > > Sub pageBreakA1C1()
> > > >
> > > > 'will find only those page breaks that were manually inserted
> > > > 'won't find those inserted automaticall by Excel
> > > >
> > > > Dim myPageBreakA1C1
> > > > myPageBreakA1C1 =
> > > > ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, ,
> xlR1C1)
> > > > Range("A3").Select
> > > > Selection = myPageBreakA1C1
> > > > Range("A4").Select
> > > > Range("A3").ClearContents
> > > >
> > > > End Sub
> > > >
> > >
> > >
> > >
>
>
>

NickHK · 02-28-2006, 09:45 PM

JCIrish
Just a guess, but you do have more than 1 page to print, without any manual
pagebreaks ?
Otherwise, there will not be any automatic page breaks ?
Also, how are you testing if they are automatic ? Something like my code
below, but with xlPageBreakAutomatic ?

NickHK

"JCIrish" <[email protected]> wrote in message
news:[email protected]...
> Thanks again, NickHk. Since I recently started with VBA, winging my way
> through it, I bought a book to help the process. I got hung up on this
> pageBreak problem, which is really tangential to my main concern, and your
> post was very helpful. I'm grateful. (I still can't, however, return page
> breaks that are automatic!)
> Thanks,
>
> JCIrish
>
> "NickHK" wrote:
>
> > JCIrish,
> > Debug (or Immediate) is one of the Windows you have available to see
direct
> > output from your app, or test results.
> > If it is not visible, go to View>Immediate
> > Look there to see the result of any Debug.Print statements.
> >
> > As for "Next", it marks the end of the "For Each..." block. Check the
help
> > for loops.
> >
> > NickHK
> >
> > "JCIrish" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi, NickHK
> > > Thanks for the very useful response. I'm new at this. The last two
lines
> > of
> > > my code were put there simply to let me step through it several
times,
> > > clearing the results and changing the HPageBreaks() subscript to
observe
> > the
> > > results. I just wanted to know where all the page breaks occur. I see
the
> > > logic of what you've written and I will try that. I've not run into
the
> > code,
> > > "Debug.Print", so, not yet having tried it, I don't know what it does.
> > Ditto
> > > for the line,"Next." Can you tell me something about these lines?
> > >
> > > Again, thanks a lot for giving a rookie a hand.
> > >
> > > JCIrish
> > >
> > > "NickHK" wrote:
> > >
> > > > JCIrish,
> > > > Not sure what you are trying to achieve with the code but you will
only
> > be
> > > > to work with HPageBreaks(2), if it exists, hence the Subscript
error.
> > > > To work with all Hbreaks:
> > > > Dim myHPageBreak As HPageBreak
> > > > For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks
> > > > With myHPageBreak
> > > > Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual)
> > > > Debug.Print "Location : " & .Location.Address(, , xlR1C1)
> > > > End With
> > > > Next
> > > > End Sub
> > > >
> > > >
> > > > "JCIrish" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > > Here is a simple code I've written that returns page break
locations,
> > but
> > > > > only those page breaks that I've inserted manually. It won't
return
> > page
> > > > > breaks that Excel inserts automatically. If I try to get these
page
> > breaks
> > > > I
> > > > > get the message, "subscript out of range. Can anybody tell me why?
And
> > how
> > > > to
> > > > > use this type of simple code to get automatic page breaks?
> > > > >
> > > > > Sub pageBreakA1C1()
> > > > >
> > > > > 'will find only those page breaks that were manually inserted
> > > > > 'won't find those inserted automaticall by Excel
> > > > >
> > > > > Dim myPageBreakA1C1
> > > > > myPageBreakA1C1 =
> > > > > ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, ,
> > xlR1C1)
> > > > > Range("A3").Select
> > > > > Selection = myPageBreakA1C1
> > > > > Range("A4").Select
> > > > > Range("A3").ClearContents
> > > > >
> > > > > End Sub
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

JCIrish · 03-01-2006, 11:20 AM

NickHK
Ah,yes.all that could be (especially multi pages to print without manual
breaks). I'll try it out. Many Thanks
JCIrish

"NickHK" wrote:

> JCIrish
> Just a guess, but you do have more than 1 page to print, without any manual
> pagebreaks ?
> Otherwise, there will not be any automatic page breaks ?
> Also, how are you testing if they are automatic ? Something like my code
> below, but with xlPageBreakAutomatic ?
>
> NickHK
>
> "JCIrish" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks again, NickHk. Since I recently started with VBA, winging my way
> > through it, I bought a book to help the process. I got hung up on this
> > pageBreak problem, which is really tangential to my main concern, and your
> > post was very helpful. I'm grateful. (I still can't, however, return page
> > breaks that are automatic!)
> > Thanks,
> >
> > JCIrish
> >
> > "NickHK" wrote:
> >
> > > JCIrish,
> > > Debug (or Immediate) is one of the Windows you have available to see
> direct
> > > output from your app, or test results.
> > > If it is not visible, go to View>Immediate
> > > Look there to see the result of any Debug.Print statements.
> > >
> > > As for "Next", it marks the end of the "For Each..." block. Check the
> help
> > > for loops.
> > >
> > > NickHK
> > >
> > > "JCIrish" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi, NickHK
> > > > Thanks for the very useful response. I'm new at this. The last two
> lines
> > > of
> > > > my code were put there simply to let me step through it several
> times,
> > > > clearing the results and changing the HPageBreaks() subscript to
> observe
> > > the
> > > > results. I just wanted to know where all the page breaks occur. I see
> the
> > > > logic of what you've written and I will try that. I've not run into
> the
> > > code,
> > > > "Debug.Print", so, not yet having tried it, I don't know what it does.
> > > Ditto
> > > > for the line,"Next." Can you tell me something about these lines?
> > > >
> > > > Again, thanks a lot for giving a rookie a hand.
> > > >
> > > > JCIrish
> > > >
> > > > "NickHK" wrote:
> > > >
> > > > > JCIrish,
> > > > > Not sure what you are trying to achieve with the code but you will
> only
> > > be
> > > > > to work with HPageBreaks(2), if it exists, hence the Subscript
> error.
> > > > > To work with all Hbreaks:
> > > > > Dim myHPageBreak As HPageBreak
> > > > > For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks
> > > > > With myHPageBreak
> > > > > Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual)
> > > > > Debug.Print "Location : " & .Location.Address(, , xlR1C1)
> > > > > End With
> > > > > Next
> > > > > End Sub
> > > > >
> > > > >
> > > > > "JCIrish" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > >
> > > > > > Here is a simple code I've written that returns page break
> locations,
> > > but
> > > > > > only those page breaks that I've inserted manually. It won't
> return
> > > page
> > > > > > breaks that Excel inserts automatically. If I try to get these
> page
> > > breaks
> > > > > I
> > > > > > get the message, "subscript out of range. Can anybody tell me why?
> And
> > > how
> > > > > to
> > > > > > use this type of simple code to get automatic page breaks?
> > > > > >
> > > > > > Sub pageBreakA1C1()
> > > > > >
> > > > > > 'will find only those page breaks that were manually inserted
> > > > > > 'won't find those inserted automaticall by Excel
> > > > > >
> > > > > > Dim myPageBreakA1C1
> > > > > > myPageBreakA1C1 =
> > > > > > ActiveWorkbook.Worksheets(1).HPageBreaks(2).Location.Address(, ,
> > > xlR1C1)
> > > > > > Range("A3").Select
> > > > > > Selection = myPageBreakA1C1
> > > > > > Range("A4").Select
> > > > > > Range("A3").ClearContents
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>