CODEWITHSUNDEEP
excelvbafooterworksheet
deexcelerator
deexcelerator

Reputation: 53

Last save date in excel worksheet footer

I am using Excel 2007 with a workbook that has many sheets. I need to have the date when the worksheet was last saved - in the footer. I was able to find the following:

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

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
With ws.PageSetup
.LeftFooter = "Last Save Time: &T"
.RightFooter = "Last Save Date: &D"
End With
Next ws

Set ws = Nothing

End Sub

This changes every worksheet. I need it to only change a sheet that has been edited (so each worksheet can have a different date). Is this even possible? Should I use a cell instead of a footer? Or do I have to create multiple workbooks?

Thanks!

Upvotes: 5

Views: 4001

Answers (3)

Doc Brown
Doc Brown

Reputation: 20044

As Remnant suggested, best approach would be to utilize the Worksheet_Change event. That means, you need this VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me.PageSetup
        .LeftFooter = "Last Change Time: " & Format(VBA.Time, "hh:mm:ss")
        .RightFooter = "Last Change Date: " & Format(VBA.Date, "dd/mm/yy")
    End With
End Sub

in every of your worksheets. This event is only raised when you change a cells content, not when you change the selection, so it may be what you want.

Remnant also wrote that this would be a "pain to set up" if you have many worksheets. I think that depends on what you call "many". For up to 20~40 worksheets, the code from above can be easily copied manually to every sheet in a few minutes.

If you already have a workbook and a lot (say, more that 40) sheets, then it may be a good idea to add the code programmatically. Add a separate module to your workbook, containing this code, and run it once:

Option Explicit
Sub InsertCode()
    Dim VBProj As Object
    Dim VBComp As Object
    Dim CodeMod As Object
    Dim sh As Worksheet
    Dim LineNum As Long

    Set VBProj = ActiveWorkbook.VBProject
    For Each sh In ThisWorkbook.Sheets
        Set VBComp = VBProj.vbcomponents(sh.Name)
        Set CodeMod = VBComp.CodeModule
        With CodeMod
            .DeleteLines 1, .CountOfLines
            LineNum = .CreateEventProc("Change", "Worksheet")
            LineNum = LineNum + 1
            .InsertLines LineNum, "Me.Pagesetup.LeftFooter = ""Last Change Time: "" & Format(VBA.Time, ""hh:mm:ss"")"
            .InsertLines LineNum + 1, "Me.Pagesetup.RightFooter = ""Last Change Date: "" & Format(VBA.Date, ""dd/mm/yy"")"
        End With
    Next
End Sub

This will add the "Worksheet_Change" event from above to the code section of every sheet.

Upvotes: 2

Alex P
Alex P

Reputation: 12487

One option might be to update the footers each time a sheet is changed. This will mean that individual sheets will have different dates / times dependent upon when they were updated.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sh.PageSetup
        .LeftFooter = "Last Save Time: " & Format(VBA.Time, "hh:mm:ss")
        .RightFooter = "Last Save Date: " & Format(VBA.Date, "dd/mm/yy")
    End With
End Sub

The user can then save the workbook as normal.

Does this gie you what you need?

Upvotes: 1

IAmTimCorey
IAmTimCorey

Reputation: 16757

The reason it modifies every worksheet is because you are doing a "For Each WS in ThisWorkbook.Worksheets". Change that to only find the worksheet you want to modify and you will be good to go.

For example, if you don't have a reference to the worksheet you want to modify, keep the For Each loop but instead of modifying each one, do a check on the sheet to see if it is the one you want. If it is, modify the footer. Otherwise, ignore it.

Upvotes: 1

Related Questions