Printing: Best Fit (1 Page Wide)

Im trying to write this small macro. I need to print a report which should be
the biggest font possible, but no more than 1 page wide. The sh is already
setup as landscape an centered. It can be any number of pages tall.

There are certain things the user could do to change the number of
rows/columns so this has to happen on the fly. I expect to use the
Workbook_BeforePrint event.

I thought of using a Do Until Loop as follows:

Sub BestFitPrint()
Sheet5.PageSetup.Zoom = 100
Do Until Sheet5.VPageBreaks.Count = 0
Sheet5.PageSetup.Zoom = Sheet5.PageSetup.Zoom - 5
Loop

Sheet5.PrintOut
End Sub

It's unlikely that the user will ever be able to print bigger than 100%

Does anyone have a better idea?
I would appreciate some help

Thanks in advance!
--
.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)

see what this does for you.
Sub zoomit()
ActiveSheet.UsedRange.Select
ActiveWindow.Zoom = True
End Sub


--
Don Guillett
SalesAid Software
[email protected]
"Pavlos" <[email protected]> wrote in message
news:[email protected]...
> Im trying to write this small macro. I need to print a report which should
be
> the biggest font possible, but no more than 1 page wide. The sh is already
> setup as landscape an centered. It can be any number of pages tall.
>
> There are certain things the user could do to change the number of
> rows/columns so this has to happen on the fly. I expect to use the
> Workbook_BeforePrint event.
>
> I thought of using a Do Until Loop as follows:
>
> Sub BestFitPrint()
> Sheet5.PageSetup.Zoom = 100
> Do Until Sheet5.VPageBreaks.Count = 0
> Sheet5.PageSetup.Zoom = Sheet5.PageSetup.Zoom - 5
> Loop
>
> Sheet5.PrintOut
> End Sub
>
> It's unlikely that the user will ever be able to print bigger than 100%
>
> Does anyone have a better idea?
> I would appreciate some help
>
> Thanks in advance!
> --
> .- -. Bye Bye
> /|6 6|\ - Pavlos
> {/(_0_)\}
> / ^ \_
> (/_/^\_\)

Don Im afraid this doesn't seem to work . It seems to change only the screen
font size not the print font size. It doesn't affect the page breaks.

Thanks anyway

"Don Guillett" wrote:

> see what this does for you.
> Sub zoomit()
> ActiveSheet.UsedRange.Select
> ActiveWindow.Zoom = True
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Pavlos" <[email protected]> wrote in message
> news:[email protected]...
> > Im trying to write this small macro. I need to print a report which should
> be
> > the biggest font possible, but no more than 1 page wide. The sh is already
> > setup as landscape an centered. It can be any number of pages tall.
> >
> > There are certain things the user could do to change the number of
> > rows/columns so this has to happen on the fly. I expect to use the
> > Workbook_BeforePrint event.
> >
> > I thought of using a Do Until Loop as follows:
> >
> > Sub BestFitPrint()
> > Sheet5.PageSetup.Zoom = 100
> > Do Until Sheet5.VPageBreaks.Count = 0
> > Sheet5.PageSetup.Zoom = Sheet5.PageSetup.Zoom - 5
> > Loop
> >
> > Sheet5.PrintOut
> > End Sub
> >
> > It's unlikely that the user will ever be able to print bigger than 100%
> >
> > Does anyone have a better idea?
> > I would appreciate some help
> >
> > Thanks in advance!
> > --
> > .- -. Bye Bye
> > /|6 6|\ - Pavlos
> > {/(_0_)\}
> > / ^ \_
> > (/_/^\_\)
>
>
>

Pavlos,
How about:
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 100
End With

The "Tall" value should be higher than the number of pages that will be
printed.

NickHK

"Pavlos" <[email protected]> wrote in message
news:[email protected]...
> Don Im afraid this doesn't seem to work . It seems to change only the
screen
> font size not the print font size. It doesn't affect the page breaks.
>
> Thanks anyway
>
> "Don Guillett" wrote:
>
> > see what this does for you.
> > Sub zoomit()
> > ActiveSheet.UsedRange.Select
> > ActiveWindow.Zoom = True
> > End Sub
> >
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Pavlos" <[email protected]> wrote in message
> > news:[email protected]...
> > > Im trying to write this small macro. I need to print a report which
should
> > be
> > > the biggest font possible, but no more than 1 page wide. The sh is
already
> > > setup as landscape an centered. It can be any number of pages tall.
> > >
> > > There are certain things the user could do to change the number of
> > > rows/columns so this has to happen on the fly. I expect to use the
> > > Workbook_BeforePrint event.
> > >
> > > I thought of using a Do Until Loop as follows:
> > >
> > > Sub BestFitPrint()
> > > Sheet5.PageSetup.Zoom = 100
> > > Do Until Sheet5.VPageBreaks.Count = 0
> > > Sheet5.PageSetup.Zoom = Sheet5.PageSetup.Zoom - 5
> > > Loop
> > >
> > > Sheet5.PrintOut
> > > End Sub
> > >
> > > It's unlikely that the user will ever be able to print bigger than
100%
> > >
> > > Does anyone have a better idea?
> > > I would appreciate some help
> > >
> > > Thanks in advance!
> > > --
> > > .- -. Bye Bye
> > > /|6 6|\ - Pavlos
> > > {/(_0_)\}
> > > / ^ \_
> > > (/_/^\_\)
> >
> >
> >

NickHK
I must say, I had thought of that. It's similar to the XL help. Im not sure
why I didn't try it - I will give it a try.

"NickHK" wrote:

> Pavlos,
> How about:
> With ActiveSheet.PageSetup
> .FitToPagesWide = 1
> .FitToPagesTall = 100
> End With
>
> The "Tall" value should be higher than the number of pages that will be
> printed.
>
> NickHK
>
> "Pavlos" <[email protected]> wrote in message
> news:[email protected]...
> > Don Im afraid this doesn't seem to work . It seems to change only the
> screen
> > font size not the print font size. It doesn't affect the page breaks.
> >
> > Thanks anyway
> >
> > "Don Guillett" wrote:
> >
> > > see what this does for you.
> > > Sub zoomit()
> > > ActiveSheet.UsedRange.Select
> > > ActiveWindow.Zoom = True
> > > End Sub
> > >
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "Pavlos" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Im trying to write this small macro. I need to print a report which
> should
> > > be
> > > > the biggest font possible, but no more than 1 page wide. The sh is
> already
> > > > setup as landscape an centered. It can be any number of pages tall.
> > > >
> > > > There are certain things the user could do to change the number of
> > > > rows/columns so this has to happen on the fly. I expect to use the
> > > > Workbook_BeforePrint event.
> > > >
> > > > I thought of using a Do Until Loop as follows:
> > > >
> > > > Sub BestFitPrint()
> > > > Sheet5.PageSetup.Zoom = 100
> > > > Do Until Sheet5.VPageBreaks.Count = 0
> > > > Sheet5.PageSetup.Zoom = Sheet5.PageSetup.Zoom - 5
> > > > Loop
> > > >
> > > > Sheet5.PrintOut
> > > > End Sub
> > > >
> > > > It's unlikely that the user will ever be able to print bigger than
> 100%
> > > >
> > > > Does anyone have a better idea?
> > > > I would appreciate some help
> > > >
> > > > Thanks in advance!
> > > > --
> > > > .- -. Bye Bye
> > > > /|6 6|\ - Pavlos
> > > > {/(_0_)\}
> > > > / ^ \_
> > > > (/_/^\_\)
> > >
> > >
> > >
>
>
>