Footer

[Guest]

Hello,

Is it possible to place in the footer of a spreadsheet the date that the
report was last modified, rather than the date the report is printed or the
current date.

Thanks,

 

[JE McGimpsey]

One way:

Put something like this in the ThisWorkbook code module:

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Excel.Range)
Sh.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy")
End Sub

Note that this will only indicate when a cell is changed due to user
input (or external data link).

Also, if there are links between multiple worksheets, you'd need to
update each worksheet, not just the worksheet that was changed.

If you're not familiar with macros, take a look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks for your respons,

I changed the code slightly to include the hour and minute, but it did not
seem to work correctly. It shows 12:00 AM as the current time.

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Excel.Range)
Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm
AMPM")
End Sub


Thank You,

 

[Guest]

And in addition to the below, how can I change the format. Such as times new
roman and 8 font (within this code)
Thank You,

 

[Dave Peterson]

Change this line:

Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM")
to
Sh.PageSetup.RightFooter = Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM")

And if you record a macro when you change the formatting of a footer, you'll see
something like:

With ActiveSheet.PageSetup
.RightFooter = "&""Times New Roman,Bold""&8asdfasdf"

So you might want something like:

Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8" & _
Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM")


But you'll have to worry about that the first character of your string is
numeric. It could confuse excel into making that font size humongous.

Just add an extra space:

Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8 " & _
Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM")


(there's a space right after the 8.)

 

[Guest]

Thanks that helps.

One more, back to JE's comment on linked worksheets. If linked worksheets
(within the file) are updated, how can the date on these sheets be updated to
show that they were modified also. (without having to touch/change each sheet)


Thank You,

 

[Dave Peterson]

J.E.'s code looked for changes that you made by typing--and excel knows what
cell you changed (and what worksheet that cell was on).

Changes caused by calculation aren't as nice. Excel doesn't keep track of the
cells that changed--or the values before the recalculation.

I think the closest thing you could do is to keep track of each value in each
cell in each worksheet. Then you could compare those "before" values with the
"after recalc" values.

It doesn't sound like much fun to me.