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, "TheObstacleIsTheP...@gmail.com"
<TheObstacleIsTheP...@gmail.com> 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 <sithiu...@gmail.com> wrote:
> On Nov 14, 8:19*am, "TheObstacleIsTheP...@gmail.com"
>
>
>
>
>
> <TheObstacleIsTheP...@gmail.com> 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!