SORRY: LEFT OUT MACRO Changing footer w/ macro

Posted by Dwight on March 21, 2001 1:00 PM

Wrote a macro to change existing footer (this would be for spreadsheets I didn't create) to one which has fullname/path on left, nothing in center, page/date on right, all in 8 point TNR font. Works fine except I can't figure where to put the "&8" for the left section to get the font size to 8 point.

ActiveSheet.PageSetup.LeftFooter = "&8Test"
ActiveSheet.PageSetup.LeftFooter = ""
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
ActiveSheet.PageSetup.CenterFooter = ""
ActiveSheet.PageSetup.RightFooter = ""
ActiveSheet.PageSetup.RightFooter = "&8Page &P of &N &D &T"

Can someone help?

Posted by mseyf on March 21, 2001 1:19 PM

try

Dim FontInfo As String

FontInfo = "&8&" & Chr(34) & "Times New Roman" & Chr(34)

ActiveSheet.PageSetup.LeftFooter = FontInfo & ActiveWorkbook.FullName
ActiveSheet.PageSetup.CenterFooter = ""
ActiveSheet.PageSetup.RightFooter = FontInfo & "Page &P of &N &D &T"

Posted by Dwight on March 21, 2001 1:53 PM

Works Great, thanks mseyf. One more question

Realized after using your language that I also want to change footer margin to .4" in the same macro. Tried:

With FooterMargin = Application.InchesToPoints(0.4)

Doesn't work. Can you help?

Posted by Mark on March 21, 2001 2:05 PM

Re: Works Great, thanks mseyf. One more question

Dwight:

try

ActiveSheet.PageSetup.FooterMargin = Application.InchesToPoints(0.4)

or change macro to:

With ActiveSheet.PageSetup
.LeftFooter = FontInfo & ActiveWorkbook.FullName
.CenterFooter = ""
.RightFooter = FontInfo & "Page &P of &N &D &T"
.FooterMargin = Application.InchesToPoints(0.4)
End With

Supposedly, the macro runs faster using 'With'

HTH

Mark

Posted by Dwight on March 21, 2001 2:35 PM

Thanks, Mark!