Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 792
deexcelerator Avatar asked Oct 11 '22 03:10

deexcelerator


2 Answers

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.

like image 62
Doc Brown Avatar answered Oct 14 '22 19:10

Doc Brown


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.

like image 41
IAmTimCorey Avatar answered Oct 14 '22 21:10

IAmTimCorey