Geek Answers Handbook

Last saved date in excel footer

I am using Excel 2007 with a workbook that has many sheets. I need to have the date when the last sheet was saved - in the footer. I managed 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 only need to change the sheet that has been edited (so that each worksheet may have a different date). Is it possible? Should I use a cell instead of a footer? Or do I need to create several books?

Thank!

+5
source share
3 answers

As Remnant said, the best approach would be to use the event Worksheet_Change. 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

. , , , .

Remnant , ", ", . , , "". 20 ~ 40 .

(, 40) , . , , :

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

"Worksheet_Change" .

+2

, , , " WS ". , , , .

, , , For Each, , , . , . .

+1

. , /, , .

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

.

, ?

+1

More articles:


All Articles