Change cell value upon save

[lockarde]

Good morning all,

I have a workbook that tracks current in progress and completed jobs for the month. I'd like two cells, C2 & D2, to update upon every save with date (C2) and time (D2), so everyone that has access can easily see when the workbook was updated. Any help is greatly appreciated!

 

[Fluff]

How about

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Sheets("Sheet1").Range("C2:D2").Value = Array(Date, Time)
End Sub

Change sheet name to suit.
This needs to go in the ThisWorkbook module

 

[lockarde]

Thanks for your quick reply Fluff! What you posted works great, but I'd like to run for every sheet in the workbook. I added a For Each loop, but it isn't running through every sheet, only updating the current worksheet. I'm thinking my syntax is off slightly. Here is what I have:

Dim wSheet As Worksheet


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  For Each wSheet In Worksheets
    Range("C2:D2").Value = Array(Date, Time)
  Next wSheet
End Sub

 

[Fluff]

It needs to be

wSheet.Range("C2:D2").Value = Array(Date, Time)

 

[lockarde]

It needs to be

wSheet.Range("C2:D2").Value = Array(Date, Time)

Oof. Doh! Thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback