[RESOLVED] How can I border each page in excel when page length could vary?

[FunkyDexter]

This sounded so easy when I sat to do it but everything I try seems to have some small but fundamental flaw.

We produce a report in excel that has some general header stuff at the top, then a row of column headers, then a load of rows of output. The header stuff and column headers I've bordered appropriately and set as the .PrintTitleRows which works nicely. I now want to place a border around the output on each page (nb. around the whole page, not the individual cells) and the problem is getting the bottom border.

To pre-empt some suggestion that don't work and maybe prompt some ideas in others, here's what I've tried/thought about so far:-

1. Outputting a fixed number of rows per page then inserting page breaks - didn't work because some of the columns need to wrap text so I can't guarantee that all the rows will fit on a page.
2. Putting a blank line with a top border at the bottom of the document and setting it as a footer - didn't work because the last page might end part way down a page and I want the border to go round the bottom row ratherthan the bottom of the page in that case.
3. Going through each row in turn to what print page it would be on and, if it's different to the last row stick in a border and page break - I think there might be something in that but I can't find a property that will tell me which page a row would print on.
4. Similar to 1 but turn wraping off and manually wrap the text down to the next row if it's too long - haven't tried this yet but how would I know the tex was too long - a string of 10 w's is longer than a string of 10 i's. Can't tell it to shrink to fit becuase the customer wantes all the text the same size.

Can anyone suggest a way round this?

[DKenny]

Funky

This can be done fairly easily using the HPageBreaks collection of the sheet in question. Here's some sample code to get you started.

Sub FunkyPageBreaks()
Dim FunkyBreak As HPageBreak
    
    For Each FunkyBreak In Worksheets(1).HPageBreaks
        With FunkyBreak.Location.Offset(-1, 0).EntireRow
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
        End With
    Next FunkyBreak
End Sub

[FunkyDexter]

Now that looks exactly what I'm after. And the opportunity to write a function called FunkyPageBreaks is just too good to pass up

[FunkyDexter]

I'm getting some funny results from that.

1. If I use a for each loop it throws a subscript out of range error
2. If I change that to a For next loop it throws a subscript out range error on element 0 (so presumably the collection has a base of 1) and on element hPageBreaks.count so I can't seem to get the last page break on.
3. The report is spread across multiple sheets. If a subsequent sheet has more pages than the first it seems to throw the subscript out of range error on any page break that's higher than the number on the first sheet - I might be missunderstanding what's happening there though.

Any ideas what's causing these subscript errors?

[DKenny]

Yes, you will get a subscript error using a FOR EACH.. loop if the PrintArea property of the PageSetup object does not have a value.
To correct it you will need to set the PrintArea before adding the borders.

Here's a code snippet where I'm setting the PrintArea to the UsedRange (wksSheet is an object variable that contains a worksheet.

wksSheet.PageSetup.PrintArea = wksSheet.UsedRange.Address

[DKenny]

Here's some more sample code. This procedure adds exterior borders to all the pages for a given worksheet in a given workbook. I haven't added error handling for invalid book or sheet names, but the rest of the code works.

Sub PageBreakBorder(BookName As String, SheetName As String)
Dim wksSheet As Worksheet
Dim rngPrint As Range
Dim lRowOffset As Long
Dim lColOffset As Long
Dim HBreak As HPageBreak
Dim VBreak As VPageBreak
Dim rngStartCell As Range
    
    Set wksSheet = Workbooks(BookName).Worksheets(SheetName)
    
    Set rngPrint = wksSheet.UsedRange
    lRowOffset = rngPrint.Rows.Count - 1
    lColOffset = rngPrint.Columns.Count - 1
    
    'Set the print area
    wksSheet.PageSetup.PrintArea = rngPrint.Address
    
    'Remove all existing borders
    rngPrint.Borders.LineStyle = xlLineStyleNone
    
    'Add Outer Borders
    rngPrint.Borders(xlEdgeTop).LineStyle = xlContinuous
    rngPrint.Borders(xlEdgeBottom).LineStyle = xlContinuous
    rngPrint.Borders(xlEdgeLeft).LineStyle = xlContinuous
    rngPrint.Borders(xlEdgeRight).LineStyle = xlContinuous
    
    'Add Horizontal Borders
    For Each HBreak In wksSheet.HPageBreaks
        Set rngStartCell = HBreak.Location
        'Add Top borders
        Range(rngStartCell, rngStartCell.Offset(0, lColOffset)).Borders(xlEdgeTop).LineStyle = xlContinuous
        'Add Bottom borders
        Range(rngStartCell.Offset(-1, 0), rngStartCell.Offset(-1, lColOffset)).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Next HBreak
    
    'Add Vertical Borders
    For Each VBreak In wksSheet.VPageBreaks
        Set rngStartCell = VBreak.Location
        'Add Left Borders
        Range(rngStartCell, rngStartCell.Offset(lRowOffset, 0)).Borders(xlEdgeLeft).LineStyle = xlContinuous
        'Add Right Borders
        Range(rngStartCell.Offset(0, -1), rngStartCell.Offset(lRowOffset, -1)).Borders(xlEdgeRight).LineStyle = xlContinuous
    Next VBreak
    
    'Clean up Object variables
    Set rngStartCell = Nothing
    Set rngPrint = Nothing
    Set wksSheet = Nothing
End Sub

[FunkyDexter]

I still can't seem to get it quite right I'm afraid. I've set the print area as suggested and I'm no longer getting odd subscript out of range errors but it seems to be finding 1 too few breaks if there's more than 1 break in the document - so if there's only 1 it's fine but if there's 3 it only finds 2. It borders the breaks it finds perfectly but just misses the last one altogether.

Here's my code:-

Private Sub pcFormatSheet()
 
    Dim pgePageBreak As HPageBreak
 
    With mExcelSheet
        'format the page
        With .PageSetup
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With
        'now put the bottom border on each page in turn
        .PageSetup.PrintArea = .Range(.Cells(1, mclngCustName), .Cells(mlngEndOutput, mclngReRun + 1)).Address
        For Each pgePageBreak In .HPageBreaks
            pgePageBreak.Location.Offset(-1, 0).EntireRow.Borders(xlEdgeBottom).Weight = xlThin
            pgePageBreak.Location.Offset(-1, 0).EntireRow.Borders(xlEdgeBottom).LineStyle = xlContinuous
        Next pgePageBreak
    End WithEnd Sub

I'm using a variable mlngEndOutput to set where the range needs to end rather than the usedRange property but I've checked it's value as it comes through and it's what I'd expect (I've also tried it with usedRange and got the same results).

It occurred to me it might be something to do with being in landscape rather than portrait but I don't think so because the breaks it sets are in the right place. Is it possible that using fitToPagesTall is somehow throwing it?

Any ideas?

>>Just had another thought. I'm setting Rows 1 to 9 as PrintTitleRows - could that be throwing it off?

[DKenny]

Funky
I think the issue you are having is around what page breaks are. Page breaks only exist between pages. The last page will have a break before but not a break afterwards. You will always need to add a border to the bottom of the report, independantly of the page break borders.

This is why, in my example code I added borders to the outer edges of the report.

'Add Outer Borders
    rngPrint.Borders(xlEdgeTop).LineStyle = xlContinuous
    rngPrint.Borders(xlEdgeBottom).LineStyle = xlContinuous
    rngPrint.Borders(xlEdgeLeft).LineStyle = xlContinuous
    rngPrint.Borders(xlEdgeRight).LineStyle = xlContinuous

[FunkyDexter]

No I'm totally happy on what pagebreaks are. There's another lump of code immediately above this one that borders the entire table. I didn't post it before to save space but this:-

.Columns(mclngTestType).ColumnWidth = 13.43
        .Columns(mclngTestNo).ColumnWidth = 6
        .Columns(mclngCands).ColumnWidth = 8.86
        .Columns(mclngDate).ColumnWidth = 8.43
        .Columns(mclngReRun).ColumnWidth = 16.86
        'border the table
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeLeft).Weight = xlThin
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeTop).LineStyle = xlContinuous
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeTop).Weight = xlThin
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeBottom).LineStyle = xlContinuous   'this bit puts the bottom edge in
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeBottom).Weight = xlThin
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeRight).LineStyle = xlContinuous
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlEdgeRight).Weight = xlThin
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlInsideVertical).LineStyle = xlContinuous
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).Borders(xlInsideVertical).Weight = xlThin
        
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngStartOutput - 1, mclngReRun)).Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngStartOutput - 1, mclngReRun)).Borders(xlEdgeBottom).Weight = xlThin
        
        'set text wrapping
        .Range(.Cells(mlngStartOutput - 1, mclngCustName), .Cells(mlngEndOutput, mclngReRun)).WrapText = True

all occurs immediately before the with .pageSetup line.

It's really odd - If I've got a 4 page report I'd expect to find 3 page breaks - but it's only finding 2.

[FunkyDexter]

Bizarre thing - I found the a post elsewhere on the net that said the active cell could throw off the count. I tried selecting the bottom right cell on my report and it now works absolutley fine.

Thanks for your help with this Declan

[DKenny]

The joys of excel

Glad you got it sorted.