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.

On Nov 14, 8:19*am, "
wrote:
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...printed_pages/.

Hope this helps,

Steve

On Nov 14, 11:10*am, Steve wrote:
On Nov 14, 8:19*am, "





wrote:
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 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!