Footer font formating in Excel

[Brian Barbre]

I wrote VB to format my footer font in excel to a certain point size. I also
have to scale the page I am printing to 1 wide by 1 tall. I want the font to
appear the same size on all the pages that I am printing, but becasue each
page is scaling differently the font appears larger or smaller when printed.

***Is there a way to scale the page being printed in excel, without scaling
the footer (or header) font.***

[STEVE BELL]

Dim zm As Single, FS As Single

zm = ActiveSheet.PageSetup.Zoom
FS = 12 * (100 / zm)
'
With ActiveSheet.PageSetup
.LeftFooter = "&" & FS & "MyFooter"
End With


amend to fit your needs...

--
steveB

Remove "AYN" from email to respond
"Brian Barbre" <Brian [email protected]> wrote in message
news:[email protected]...
>I wrote VB to format my footer font in excel to a certain point size. I
>also
> have to scale the page I am printing to 1 wide by 1 tall. I want the font
> to
> appear the same size on all the pages that I am printing, but becasue each
> page is scaling differently the font appears larger or smaller when
> printed.
>
> ***Is there a way to scale the page being printed in excel, without
> scaling
> the footer (or header) font.***

[Brian Barbre]

Steve,
Thank you for the quick response. The VBA code you gave made sense and I
think is a great solution. However, I had a problem when I tried to apply it
to my situation. I have a template in excel I am printing that expands or
contracts when it runs through a print macro depending on the data it is
reporting. In order to best fit the page for printing I have the scaling set
to 1 page wide by 1 page tall. zm = ActiveSheet.PageSetup.Zoom therefore
returns a 0. I can't figure out how to switch the scaling to zoom using VBA
(I tried using ActiveSheet.PageSetup.Zoom = True but it doesn't work). Any
thoughts about an alternative solution?

Thanks so much
Brian

"STEVE BELL" wrote:

> Dim zm As Single, FS As Single
>
> zm = ActiveSheet.PageSetup.Zoom
> FS = 12 * (100 / zm)
> '
> With ActiveSheet.PageSetup
> .LeftFooter = "&" & FS & "MyFooter"
> End With
>
>
> amend to fit your needs...
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "Brian Barbre" <Brian [email protected]> wrote in message
> news:[email protected]...
> >I wrote VB to format my footer font in excel to a certain point size. I
> >also
> > have to scale the page I am printing to 1 wide by 1 tall. I want the font
> > to
> > appear the same size on all the pages that I am printing, but becasue each
> > page is scaling differently the font appears larger or smaller when
> > printed.
> >
> > ***Is there a way to scale the page being printed in excel, without
> > scaling
> > the footer (or header) font.***
>
>
>

[STEVE BELL]

Brian,

My bad - didn't take that into account.

The only other way that I can think of is to count the number of used rows
and compare that to
the number of rows it would print at zoom = 100

You can do that with something like usedrange.rows.count

or Lastrow = Cells(Rows.COUNT, "A").End(xlUp).Row
(Set A for the column that will always have an entry in the last row.

or LastRow = Cells.SpecialCells(xlLastCell).Row
(be careful with this one because it will count any row that had an entry
and than had the entry cleared)


Say Lastrow = 100 and normal number of rows is 56
FS = 12*100/56

but this would just be an approximation because of the added affect of the
number of columns.
--
steveB

Remove "AYN" from email to respond
"Brian Barbre" <Brian [email protected]> wrote in message
news:[email protected]...
> Steve,
> Thank you for the quick response. The VBA code you gave made sense and I
> think is a great solution. However, I had a problem when I tried to apply
> it
> to my situation. I have a template in excel I am printing that expands or
> contracts when it runs through a print macro depending on the data it is
> reporting. In order to best fit the page for printing I have the scaling
> set
> to 1 page wide by 1 page tall. zm = ActiveSheet.PageSetup.Zoom therefore
> returns a 0. I can't figure out how to switch the scaling to zoom using
> VBA
> (I tried using ActiveSheet.PageSetup.Zoom = True but it doesn't work).
> Any
> thoughts about an alternative solution?
>
> Thanks so much
> Brian
>
> "STEVE BELL" wrote:
>
>> Dim zm As Single, FS As Single
>>
>> zm = ActiveSheet.PageSetup.Zoom
>> FS = 12 * (100 / zm)
>> '
>> With ActiveSheet.PageSetup
>> .LeftFooter = "&" & FS & "MyFooter"
>> End With
>>
>>
>> amend to fit your needs...
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "Brian Barbre" <Brian [email protected]> wrote in message
>> news:[email protected]...
>> >I wrote VB to format my footer font in excel to a certain point size. I
>> >also
>> > have to scale the page I am printing to 1 wide by 1 tall. I want the
>> > font
>> > to
>> > appear the same size on all the pages that I am printing, but becasue
>> > each
>> > page is scaling differently the font appears larger or smaller when
>> > printed.
>> >
>> > ***Is there a way to scale the page being printed in excel, without
>> > scaling
>> > the footer (or header) font.***
>>
>>
>>

[Brian Barbre]

Steve,
What I ended up doing was taking your solution along with using other code
that was able to retreive the Zoom percentage using "SendKeys"and it worked
out nicely.

Thanks

"STEVE BELL" wrote:

> Brian,
>
> My bad - didn't take that into account.
>
> The only other way that I can think of is to count the number of used rows
> and compare that to
> the number of rows it would print at zoom = 100
>
> You can do that with something like usedrange.rows.count
>
> or Lastrow = Cells(Rows.COUNT, "A").End(xlUp).Row
> (Set A for the column that will always have an entry in the last row.
>
> or LastRow = Cells.SpecialCells(xlLastCell).Row
> (be careful with this one because it will count any row that had an entry
> and than had the entry cleared)
>
>
> Say Lastrow = 100 and normal number of rows is 56
> FS = 12*100/56
>
> but this would just be an approximation because of the added affect of the
> number of columns.
> --
> steveB
>
> Remove "AYN" from email to respond
> "Brian Barbre" <Brian [email protected]> wrote in message
> news:[email protected]...
> > Steve,
> > Thank you for the quick response. The VBA code you gave made sense and I
> > think is a great solution. However, I had a problem when I tried to apply
> > it
> > to my situation. I have a template in excel I am printing that expands or
> > contracts when it runs through a print macro depending on the data it is
> > reporting. In order to best fit the page for printing I have the scaling
> > set
> > to 1 page wide by 1 page tall. zm = ActiveSheet.PageSetup.Zoom therefore
> > returns a 0. I can't figure out how to switch the scaling to zoom using
> > VBA
> > (I tried using ActiveSheet.PageSetup.Zoom = True but it doesn't work).
> > Any
> > thoughts about an alternative solution?
> >
> > Thanks so much
> > Brian
> >
> > "STEVE BELL" wrote:
> >
> >> Dim zm As Single, FS As Single
> >>
> >> zm = ActiveSheet.PageSetup.Zoom
> >> FS = 12 * (100 / zm)
> >> '
> >> With ActiveSheet.PageSetup
> >> .LeftFooter = "&" & FS & "MyFooter"
> >> End With
> >>
> >>
> >> amend to fit your needs...
> >>
> >> --
> >> steveB
> >>
> >> Remove "AYN" from email to respond
> >> "Brian Barbre" <Brian [email protected]> wrote in message
> >> news:[email protected]...
> >> >I wrote VB to format my footer font in excel to a certain point size. I
> >> >also
> >> > have to scale the page I am printing to 1 wide by 1 tall. I want the
> >> > font
> >> > to
> >> > appear the same size on all the pages that I am printing, but becasue
> >> > each
> >> > page is scaling differently the font appears larger or smaller when
> >> > printed.
> >> >
> >> > ***Is there a way to scale the page being printed in excel, without
> >> > scaling
> >> > the footer (or header) font.***
> >>
> >>
> >>
>
>
>

[STEVE BELL]

Excellent! Glad I was able to add a little something to your solution.

--
steveB

Remove "AYN" from email to respond
"Brian Barbre" <[email protected]> wrote in message
news:[email protected]...
> Steve,
> What I ended up doing was taking your solution along with using other code
> that was able to retreive the Zoom percentage using "SendKeys"and it
> worked
> out nicely.
>
> Thanks
>
> "STEVE BELL" wrote:
>
>> Brian,
>>
>> My bad - didn't take that into account.
>>
>> The only other way that I can think of is to count the number of used
>> rows
>> and compare that to
>> the number of rows it would print at zoom = 100
>>
>> You can do that with something like usedrange.rows.count
>>
>> or Lastrow = Cells(Rows.COUNT, "A").End(xlUp).Row
>> (Set A for the column that will always have an entry in the last row.
>>
>> or LastRow = Cells.SpecialCells(xlLastCell).Row
>> (be careful with this one because it will count any row that had an entry
>> and than had the entry cleared)
>>
>>
>> Say Lastrow = 100 and normal number of rows is 56
>> FS = 12*100/56
>>
>> but this would just be an approximation because of the added affect of
>> the
>> number of columns.
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "Brian Barbre" <Brian [email protected]> wrote in message
>> news:[email protected]...
>> > Steve,
>> > Thank you for the quick response. The VBA code you gave made sense and
>> > I
>> > think is a great solution. However, I had a problem when I tried to
>> > apply
>> > it
>> > to my situation. I have a template in excel I am printing that expands
>> > or
>> > contracts when it runs through a print macro depending on the data it
>> > is
>> > reporting. In order to best fit the page for printing I have the
>> > scaling
>> > set
>> > to 1 page wide by 1 page tall. zm = ActiveSheet.PageSetup.Zoom
>> > therefore
>> > returns a 0. I can't figure out how to switch the scaling to zoom
>> > using
>> > VBA
>> > (I tried using ActiveSheet.PageSetup.Zoom = True but it doesn't work).
>> > Any
>> > thoughts about an alternative solution?
>> >
>> > Thanks so much
>> > Brian
>> >
>> > "STEVE BELL" wrote:
>> >
>> >> Dim zm As Single, FS As Single
>> >>
>> >> zm = ActiveSheet.PageSetup.Zoom
>> >> FS = 12 * (100 / zm)
>> >> '
>> >> With ActiveSheet.PageSetup
>> >> .LeftFooter = "&" & FS & "MyFooter"
>> >> End With
>> >>
>> >>
>> >> amend to fit your needs...
>> >>
>> >> --
>> >> steveB
>> >>
>> >> Remove "AYN" from email to respond
>> >> "Brian Barbre" <Brian [email protected]> wrote in
>> >> message
>> >> news:[email protected]...
>> >> >I wrote VB to format my footer font in excel to a certain point size.
>> >> >I
>> >> >also
>> >> > have to scale the page I am printing to 1 wide by 1 tall. I want
>> >> > the
>> >> > font
>> >> > to
>> >> > appear the same size on all the pages that I am printing, but
>> >> > becasue
>> >> > each
>> >> > page is scaling differently the font appears larger or smaller when
>> >> > printed.
>> >> >
>> >> > ***Is there a way to scale the page being printed in excel, without
>> >> > scaling
>> >> > the footer (or header) font.***
>> >>
>> >>
>> >>
>>
>>
>>