VBA: HPageBreaks.Location

[Domenic]

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!

 

[shg]

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:

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 ...

 

[Domenic]

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:

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?

 

[Norie]

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.

 

[Norie]

Wait a minute, Set does work - I must have had a typo or something in the original code.

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?

 

[Norie]

This is getting weirder.

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

    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.

 

[Domenic]

Thanks Norie, very much appreciated!

This is getting weirder.

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

    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.

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!

 

[shg]

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.

 

[Domenic]

I woke up at 2AM realizing #3 must be true, but decided my wife would not appreciate me running upstairs to test or reply.