"Last modified by" in Excel Footer

I have a footer that I place in all my Word documents it goes a little something like this:

Created by (insert name) on (insert dated) - Last modified by (insert name) on (insert date)

I would really like to design an excel footer that does the same thing but I can't work out how.

Any suggestions?

If you add this macro on the workbook it will add the last modified everytime the workbook is saved...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Integer

For i = 1 To Worksheets.Count
With Worksheets(1)
.PageSetup.RightFooter = "Last Modified By: " & Application.UserName & " On: " & Format(Now, "m/d/yy")
End With
Next i



End Sub

Hope that helps...

I'm having trouble with this macro. How exactly do I create it? When I go Alt-F8 I get the macro window but "create" is greyed out. I tried recording a new macro and then editing it and copy/paste in the marco from your above post but it wouldn't let me do this.

Thanks for your help.

Originally Posted by Muz

I'm having trouble with this macro. How exactly do I create it? When I go Alt-F8 I get the macro window but "create" is greyed out. I tried recording a new macro and then editing it and copy/paste in the marco from your above post but it wouldn't let me do this.

Thanks for your help.

Press Alt/F11 (or rightmouse the sheet tab and select 'View Code', then select ThisWorkbook

Copy the code and Paste it into the code area (of ThisWorkbook)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim iW As Integer
For iW = 1 To Worksheets.Count
With Worksheets(iW)
.PageSetup.RightFooter = "Last Modified By: " & Application.UserName & " On: " & Format(Now, "m/d/yy")
End With
Next iW
End Sub

I have been looking for something like this for a 'template' so that many users of a sdocument will know who 'last saved' the excel 2007 document
I saved this as a macro enabled template and it works a treat.....
Except that if you have more than one 'sheet' it falls back to nothing in the footer
So can anyone help with this coding to make it happen for all sheets
It would be great if it could also have the origional creator of the document as well as the 'last saved by'