Multiple lines in Footer with VBA

[Art]

I am using the following VBA macro that I saw in online.
It is great, but have 2 questions.
Operating system is Windows XP and 2000, Excel version is
currently 97


Sub workbook_beforeprint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter =
ActiveWorkbook.FullName
ActiveSheet.PageSetup.RightFooter = " Printed on " &
Format(Date, "mm-dd-yyyy") & " " & Format
(Time, "hh:mm AMPM")
End Sub

Question 1: What do I need do to have the date and time
on a separate line from the path and filename? Sometimes
the path and filename have been long and run into the
date & time.

I would like:
Path\filename on one line, and
Date and Time on another line


Question 2: How can I set the font for this VBA footer? I
know this should be easy but I have tried and cannot find
the solution.

Thank You
Art

 

[Frank Kabel]

Hi
try:
Sub workbook_beforeprint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter =
ActiveWorkbook.FullName
ActiveSheet.PageSetup.RightFooter = " Printed on " &
Format(Date, "mm-dd-yyyy") & " " & vblf & Format
(Time, "hh:mm AMPM")
End Sub

Regarding the font setting: Just try recording a macro while doing this
manually and have a look at the code

 

[Art]

Frank,

Thank you.
The vblf & works great.
I was able to set font for the right footer but CANNOT
get it set for the
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
I will keep trying.

Thanks again
Art

 

[Dave Peterson]

I recorded a macro when I changed the font for a footer. I got this:

With ActiveSheet.PageSetup
.LeftFooter = "&8asdfasdf"
End With

so maybe...

With ActiveSheet.PageSetup
.LeftFooter = "&8" & activeworkbook.fullname
End With

would work ok for you. (record a macro if you change the font name to get it
all.)

One warning. If you ever concatenate a string that starts with a number and
include the font size, then include a space

With ActiveSheet.PageSetup
.LeftFooter = "&8 " & "2 will go!"
End With

Without that space, the font size will be 82. (and you'll see " will go" as the
footer.)

 

[Art]

Dave / Frank,

Thank you for your input. The problem that I am having
appears to be when I have the split line. That is when I
put in the vblf. Below is my VBA for the footer. Whatever
I do seems to produce a VBA error. Any suggestions?

Thanks again.
Art

ActiveSheet.PageSetup.LeftFooter =
ActiveWorkbook.FullName & vbLf & " Printed on " & Format
(Date, "mm-dd-yyyy") & " at " & Format(Time, "hh:mm AMPM")

 

[Art]

Second try. Below is what I have tried only to get VBA
error.

ActiveSheet.PageSetup.LeftFooter = "&""Times New
Roman,Regular""&8"ActiveWorkbook.FullName & vbLf & "
Printed on " & Format(Date, "mm-dd-yyyy") & " at " &
Format(Time, "hh:mm AMPM")

once again thank you for your help
Art

 

[Dave Peterson]

You need an ampersand right before activeworkbook.fullname:

ActiveSheet.PageSetup.LeftFooter _
= "&""Times New Roman,Regular""&8" & ActiveWorkbook.FullName _
& vbLf & "Printed on " & Format(Date, "mm-dd-yyyy") _
& " at " & Format(Time, "hh:mm AMPM")

 

[Art]

Dave,

THANK YOU
So simple and yet so . . .
Once again THANK YOU.

Art