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.***

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 wrote in message
...
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,
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 wrote in message
...
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,

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 wrote in message
...
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 wrote in message
...
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,
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 wrote in message
...
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 wrote in message
...
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.***

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

--
steveB

Remove "AYN" from email to respond
"Brian Barbre" wrote in message
...
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 wrote in message
...
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 wrote in
message
...
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.***

Hi

Any chance you can advise how you got around this by posting the code as
this is what I am trying to achieve and have not been able to find this
anywhere.

Thanks, Nav

"STEVE BELL" wrote:

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

--
steveB

Remove "AYN" from email to respond
"Brian Barbre" wrote in message
...
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 wrote in message
...
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 wrote in
message
...
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.***