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!
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With