Auto height adjustment to fit on a single printed page

[TheObstacleIsThePath]

Here's my latest puzzle:

I have a form in Excel 2003 that is populated by word-wrapped text
fields with a variable length (and ultimately variable height). I
have a macro that will auto-fit row height on these fields to make
things tidy.

The form also has something equivalent to a footer that I need to
force to the bottom of a printed page. I manually accomplish this by
stretching the last row, and checking page-preview to confirm that
footer is the last thing on page 1.

Is there a way to poll excel for the number of pages a spreadsheet
will take if printed? With this, I could write a macro to
incrementally decrease a row height until the number of pages falls to
1.

Using a word mailmerge or proper excel footers is not really practical
in this case.

Any hints would be appreciated.

 

[Steve]

Here's my latest puzzle:

I have a form in Excel 2003 that is populated by word-wrapped text
fields with a variable length (and ultimately variable height).  I
have a macro that will auto-fit row height on these fields to make
things tidy.

The form also has something equivalent to a footer that I need to
force to the bottom of a printed page.  I manually accomplish this by
stretching the last row, and checking  page-preview to confirm that
footer is the last thing on page 1.

Is there a way to poll excel for the number of pages a spreadsheet
will take if printed?  With this, I could write a macro to
incrementally decrease a row height until the number of pages falls to
1.

Using a word mailmerge or proper excel footers is not really practical
in this case.

Any hints would be appreciated.

By doing a search on google for excel vba get number of printed pages,
I came across this:

Sub NumberOfPrintedPages()
Worksheets(1).DisplayAutomaticPageBreaks = True
HorizBreaks = Worksheets(1).HPageBreaks.Count
HPages = HorizBreaks + 1
VertBreaks = Worksheets(1).VPageBreaks.Count
VPages = VertBreaks + 1
NumPages = HPages * VPages
Worksheets(1).DisplayAutomaticPageBreaks = False
MsgBox NumPages
End Sub

I found this on http://spreadsheetpage.com/index.php/tip/determining_the_number_of_printed_pages/.

Hope this helps,

Steve

 

[TheObstacleIsThePath]

By doing a search on google for excel vba get number of printed pages,
I came across this:

Sub NumberOfPrintedPages()
    Worksheets(1).DisplayAutomaticPageBreaks = True
    HorizBreaks = Worksheets(1).HPageBreaks.Count
    HPages = HorizBreaks + 1
    VertBreaks = Worksheets(1).VPageBreaks.Count
    VPages = VertBreaks + 1
    NumPages = HPages * VPages
    Worksheets(1).DisplayAutomaticPageBreaks = False
    MsgBox NumPages
End Sub

I found this onhttp://spreadsheetpage.com/index.php/tip/determining_the_number_of_pr....

Hope this helps,

Steve- Hide quoted text -

- Show quoted text -

Works like a charm. Thanks for the resource!