VBA: HPageBreaks.Location

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
21,516
Office Version
  1. 365
Platform
  1. Windows
Does anyone know why the following code does not move the first horizontal page break to the specified location?

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> test()<br><br>    <SPAN style="color:#00007F">Set</SPAN> CurrCell = ActiveCell<br>    <br>    Cells(Rows.Count, Columns.Count).Select<br>    <br>    ActiveSheet.ResetAllPageBreaks<br>    <br>    Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range("e5")<br><br>    CurrCell.Select<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

It seems to work if I replace...

<font face=Calibri>    Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range("e5")</FONT>

with

<font face=Calibri>Worksheets(1).HPageBreaks.Add Worksheets(1).Range("e5")</FONT>

So why doesn't it work with the Location property?

Thanks!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
If there are no HPBs (i.e., the used range all fits on a single sheet), then that line generates a run-time (subscript) error -- that makes sense.

If rows 1:4 are so tall (or the vertical margins so large) that there's an automatic page break before row 5, that line can't change the position -- that makes sense.

But if neither of those is true, it still doesn't move -- that escapes me.

I may be losing it, but fill A1:E20 with =CELL("address", A1) and run this:

Code:
Sub test()
    With Worksheets(1)
        .ResetAllPageBreaks
        .HPageBreaks.Add Range("E5")
        .HPageBreaks(1).Location = .Range("E10")
    End With
End Sub

Look what's in A5 after you run it ...
 
Last edited:
Upvote 0
Thanks for your help, shg, I really appreciate it!

If there are no HPBs (i.e., the used range all fits on a single sheet), then that line generates a run-time (subscript) error -- that makes sense.

Makes sense. However, my used range extends beyond the first page.

If rows 1:4 are so tall (or the vertical margins so large) that there's an automatic page break before row 5, that line can't change the position -- that makes sense.

Yes, this makes sense too. However, again, this is not the case.

I may be losing it, but fill A1:E20 with =CELL("address", A1) and run this:

Code:
Sub test()
    With Worksheets(1)
        .ResetAllPageBreaks
        .HPageBreaks.Add Range("E5")
        .HPageBreaks(1).Location = .Range("E10")
    End With
End Sub

Look what's in A5 after you run it ...

I see that it places the contents of E10 at the first horizontal page break in A5. Obviously this is not the desired result. So I'm not sure what to make of it. :) Bug? If you try the following code, you'll see that the second horizontal page break does not move, as expected...

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> test()<br><br>    Range("a1:a200").FormulaR1C1 = "=ROW(RC)"<br><br>    <SPAN style="color:#00007F">Set</SPAN> CurrCell = ActiveCell<br>    <br>    Cells(Rows.Count, Columns.Count).Select<br>    <br>    ActiveSheet.ResetAllPageBreaks<br>    <br>    ActiveSheet.HPageBreaks(2).Location = ActiveSheet.Range("a80")<br><br>    CurrCell.Select<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Any comments?
 
Last edited:
Upvote 0
This doesn't really make sense at all, Location returns a range so when you use something like this:

Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range("e5")

It's putting the value from E5 into the location of the page break, not changing the actual location.

However it does say in Help, and the Object Browser indicates it too, that it should change the location.

I've tried using Set but that errored, and obviously when you try the address as a string if puts the string in the location.
 
Upvote 0
Wait a minute, Set does work - I must have had a typo or something in the original code.:oops:

Wait another minute it only seems to work when you are in Page Break Preview mode.

So how do you do that without manually changing to that mode?:)
 
Upvote 0
This is getting weirder.

I found out how to change to page break mode in code and tried this.

Code:
    Range("a1:a200").FormulaR1C1 = "=ROW()"
 
    Worksheets("Sheet4").HPageBreaks.Add Worksheets("Sheet4").Range("A30")
 
    ActiveWindow.View = xlPageBreakPreview
    Set Worksheets("Sheet4").HPageBreaks(Worksheets("Sheet4").HPageBreaks.Count).Location = Worksheets("Sheet4").Range("A45")
    ActiveWindow.View = xlNormalView
It ran but it added a new page break on row 45.:eek:
 
Upvote 0
Thanks Norie, very much appreciated!

This is getting weirder.

I found out how to change to page break mode in code and tried this.

Code:
    Range("a1:a200").FormulaR1C1 = "=ROW()"
 
    Worksheets("Sheet4").HPageBreaks.Add Worksheets("Sheet4").Range("A30")
 
    ActiveWindow.View = xlPageBreakPreview
    Set Worksheets("Sheet4").HPageBreaks(Worksheets("Sheet4").HPageBreaks.Count).Location = Worksheets("Sheet4").Range("A45")
    ActiveWindow.View = xlNormalView
It ran but it added a new page break on row 45.:eek:

Actually, this seems to work. It moves the last horizontal page break to Row 45. And, in doing so, it re-calculates subsequent page breaks to their default settings. It looks like this is probably the intended result in this circumstance. However, if a page break is moved within page breaks on either side of it, it works as expected.

So there are three things that I've learned...

  1. It's not necessary to "add code to select the last cell used in the worksheet before the code uses the Location property of horizontal or vertical page breaks", as described in http://support.microsoft.com/kb/210663.
  2. It's necessary to change the view to page break mode before using the 'Location' property.
  3. Unlike the example given in the help file for the 'Location' property, it's necessary to use the 'Set' statement to assign a range reference to the 'Location' property.

Shg, Norie, thanks again!
 
Upvote 0
  1. It's not necessary to "add code to select the last cell used in the worksheet before the code uses the Location property of horizontal or vertical page breaks", as described in http://support.microsoft.com/kb/210663.
  2. It's necessary to change the view to page break mode before using the 'Location' property.
  3. Unlike the example given in the help file for the 'Location' property, it's necessary to use the 'Set' statement to assign a range reference to the 'Location' property.
I woke up at 2AM realizing #3 must be true, but decided my wife would not appreciate me running upstairs to test or reply.

I would have never figured out #2.

Norie, Domenic, thanks.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top