Print preview error from BeforePrint macro

[LauriS]

Using the WONDERFUL knowledge this board is loaded with I was able to answer
a question that came up in my training class. How do you get multiple lines
in your footers and how do you adjust the font size.

I came up with this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "&07" & ActiveWorkbook.FullName & vbLf
& vbLf
End Sub

Using this they can put the page numbering info in the center footer center
(via the menus) and then the macro will put the full path name in a smaller
font (it's a HUGE path name) in the left section of the footer everything is
great.

This prints perfectly ... but when I try to print preview the sheet I get
the following error message:

Method 'FullName' of object '_Workbook' failed

If I pick debug it goes on just fine but I'd rather not have the error.

Is there someplace else I can put the macro that wouldn't cause this error
on previewing??

 

[Dave Peterson]

Your code worked ok for me just as you wrote it.

You sure it's that line that's causing the error.

ps. I'd use this instead:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "&07" & Me.FullName & vbLf & vbLf
End Sub

Me is a special keyword that refers to the thing that owns the code. In this
case, it means the workbook that you're printing (probably).

pps. Actually, I'd specify the sheets that I wanted to have that footer. You
can print other stuff besides just the activesheet.

If you wanted all the sheets to have that footer:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
dim sh as object
for each sh in me.sheets
sh.PageSetup.LeftFooter = "&07" & me.FullName & vbLf & vbLf
next sh
End Sub