changing font type in a footer

[Guest]

I found VBA code that would place a cell's contents into the footer:

With wkSht.PageSetup
.CenterFooter = wkSht.Range("F1").Value
End With

I also need to change the font type to Times New Roman, Italic. I tried
"&""Times New Roman,Italic" both before and after the above command on its
own line and on the same line, but it either has no effect or prevents the
footnote from showing up. What code do I use to change the font type along
with the above code? Thanks.

 

[Dave Peterson]

..CenterFooter = "&""Times New Roman,Italic""" & wksht.Range("f1").Value

 

[Guest]

Thanks. Now I'm also trying to control the font size. I want the font size
of the footer to be the same on several different worksheets, all zoomed to
different sizes, since they are set to 1 page by 1 page with differing
numbers and sizes of rows and columns. I tried to set the footer to a given
size and then adjust the footer size based on the zoom, but even using a font
size of 1 for some reason yields text that is too large.

Is there a way to get a font size smaller than one, or better yet, what code
would I use to determine the zoom percentage and then standardize my footer
text size?

Thanks your help.

 

[Dave Peterson]

Jim Rech and Nick Osdale-Popa posted this that might help get the zoom factor.
http://groups.google.com/groups?threadm=ejuo3kjsAHA.2344@tkmsftngp05

Good luck,

 

[Guest]

I took a look at that post and incorporated it into my code, but I'm still
getting a 0 for the zoom factor. I'm not familiar with SendKeys, so I'm not
sure what that's doing, but it's clearly returning 0 since it's set to fit to
x pages wide by x pages tall.

Could you please help out? Thanks.

 

[Dave Peterson]

The first sendkeys sends commands (%C = Alt-C) to close the print preview
window.

SendKeys "%C"
ActiveSheet.PrintPreview

The second sends P-alt-A-enter. (Select the Page tab, then the Adjust to
section, then hits enter to leave the dialog.


'to get/set the Zoom %, initiate the Page Setup Dialog box.
SendKeys "P%A~"
Application.Dialogs(xlDialogPageSetup).Show

I don't see how it could be returning 0.

I took a look at that post and incorporated it into my code, but I'm still
getting a 0 for the zoom factor. I'm not familiar with SendKeys, so I'm not
sure what that's doing, but it's clearly returning 0 since it's set to fit to
x pages wide by x pages tall.

Could you please help out? Thanks.