Formulas in Excel Footer?

[panicbutton]

Hey there,

i need some VBA help...
What I am looking for is a footer that is automatically populated with the following information:

Left footer:
<table width="64" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">=Sheet5!AD234</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">=Sheet1!B45</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">=today()</td> </tr> </tbody></table>
center footer:
Page number

Please note, that the workbook as multiple worksheets, but I want this macro only to run over - lets say - Sheet3.

My next question would be, how can we trigger this macro? Is there a way to start if before the user hits 'print', 'save' or ' 'save as pdf'?

thanks a lot for your help!

 

[Gary McMaster]

In the Workbook_BeforePrint event.

Hope it helps.

Gary

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If ActiveSheet.Name = "Sheet3" Then

    ActiveSheet.PageSetup.LeftFooter = Worksheets("Sheet5").Range("AD234").Value & " " & Worksheets("Sheet1").Range("B45").Value & " " & Date
    ActiveSheet.PageSetup.CenterFooter = "Page &P"

End If

End Sub

 

[panicbutton]

Thanks Gary.
Is there a way to have multiple lines in the left footer below each other instead of only the one as in your macro?

also, can I trigger the Macro automatically when the workbook is saved? I probably should have said so, but printing of the worksheet would rather be the exception.
Thanks!

 

[Gary McMaster]

There is also a Workbook_BeforeSave event. You can put the same code in there.

You can embed the constant vbcrlf (carriage return / line feed) in the string to force linefeeds. Like so:

"This is a" & vbcrlf & "Test"

The word test would be on a new line.

Gary

 

[Gary McMaster]

Make that just vblf

vbcrlf gives 2 linefeeds.

Here it is modified:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If ActiveSheet.Name = "Sheet3" Then

    ActiveSheet.PageSetup.LeftFooter = Worksheets("Sheet5").Range("AD234").Value & vbLf & Worksheets("Sheet1").Range("B45").Value & vbLf & Date
    ActiveSheet.PageSetup.CenterFooter = "Page &P"

End If

End Sub

For the "before save" event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If ActiveSheet.Name = "Sheet3" Then

    ActiveSheet.PageSetup.LeftFooter = Worksheets("Sheet5").Range("AD234").Value & vbLf & Worksheets("Sheet1").Range("B45").Value & vbLf & Date
    ActiveSheet.PageSetup.CenterFooter = "Page &P"

End If

End Sub

Gary

 

[panicbutton]

I will try that.
Thank you!