Search code examples
excelpage-breakvba

excel 2007 vba: how to refer to HPageBreaks

I'm trying to write a macro which can look into the list of horizontal page breaks that a worksheet keeps, and it seems like HPageBreaks should be exactly that. I can add or remove page breaks from it, but I can't seem to isolate the collection itself to look at its contents. Even adding a watch and looking at ActiveSheet.HPageBreaks just brings up a generic looking object with a count field equal to 0 regardless of existing page breaks.

I'm really confused about this now. Is there any way to look into the existing page breaks within a sheet? A listing of what rows they occur on/between would be great.

Solution

  • This should get you started:

    Sub testing()
    MsgBox "There are " & ActiveSheet.HPageBreaks.Count & " pagebreaks."
    For Each pb In ActiveSheet.HPageBreaks
        MsgBox "a page break lies between rows " & pb.Location.Row - 1 _
            & " and " & pb.Location.Row
    Next
End Sub

    Here are some (rather scanty) references.:

    http://msdn.microsoft.com/en-us/library/aa661442(office.10).aspx

    http://msdn.microsoft.com/en-us/library/aa206426(office.10).aspx