PDA

View Full Version : update page footer



koala
05-17-2010, 01:56 AM
Hi Forum,

I have the following code in the "ThisWorkbook" module to update my footers each time I print the page, however it only updates if I print one page, or print preview before printing. I often print a group of pages, and want all pages to update before printing.

Can someone steer me in the right direction?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.CenterFooter = "&8This file was last modified on: " & Format(Me.BuiltinDocumentProperties("last save time"), "ddd, dd mmm yyyy hh:mm")
End With

With ActiveSheet.PageSetup
.RightFooter = "&8 Printed by " & Application.UserName & " on " & Format(Date, "ddd, dd mmmm yyyy") & " at " & Format(Time, "hh:mm")

End With


End Sub


cheers
Koala

Aussiebear
05-17-2010, 03:11 AM
I would say its because you are using

With ActiveSheet.PageSetup
BTW...........Your face is familier, do I know you?:devil2:

koala
05-18-2010, 12:35 AM
Thanks Aussiebear,

I am not very skilled at VBA, and have been reading to try and find another way, but havent been able to work it out.

I want the selected sheets to update, however even though they are selected, they obviously are not all active (only the last one selected is).

Can you assist further?

cheers
Koala

oh, and yes ..... you do look familiar :friends:

rbrhodes
05-18-2010, 01:41 AM
Hi k,

Something like:

[edit]

Untested!




Private Sub Workbook_BeforePrint(Cancel As Boolean)


dim sht as worksheet


for each sht in selected.sheets
With sht.PageSetup
.CenterFooter = "&8This file was last modified on: " & Format(Me.BuiltinDocumentProperties("last save time"), "ddd, dd mmm yyyy hh:mm")
.RightFooter = "&8 Printed by " & Application.UserName & " on " & Format(Date, "ddd, dd mmmm yyyy") & " at " & Format(Time, "hh:mm")


End With
next sht


set sht = nothing

End Sub



'Do I know you?' a classic...

Aussiebear
05-18-2010, 10:22 PM
'Do I know you?' a classic...

Well its a bit like that there them gum leaves..... They might look the same, but as us bears know, Apples just ain't apples.