Custom Document Properties - Last Calculation Date

[Guest]

Hi

I would like to include in my project details on document properties such as
last date saved (which I understand is a standard document property) but I
would also like to include a date for the last time the spreadsheet was
"calculated". I have turned off automatic calculations during
updating/adding records and on saving. The reports that are produced form
the calculations are not always needed so I only want to calculate them when
the user needs them. Therefore I would like to be able to display on a
userform, the last date and time the spreadsheet was amended (ie records
added) and the last date and time it was calculated. Is this possible with
Custom document Properties does any body know?!?!

 

[Tim Barlow]

Zani,

You can use the calculate event to detemine when each sheet was last
calculated - e.g. the following puts the date & time on sheet that was
re-calculated

This one in the 'ThisWorkbook' module to capture all re-calculations:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Range("A1") = Format(Now(), "dd mmm yy")
Sh.Range("A2") = Format(Now(), "hh:mm")
End Sub

Or this one in the Sheet module to capture re-calculations on that sheet
only:

Private Sub Worksheet_Calculate()
Me.Range("A1") = Format(Now(), "dd mmm yy")
Me.Range("A2") = Format(Now(), "hh:mm")
End Sub

You could also use the Workbook_SheetChange or the Worksheet_Change events
to get the time of the last modification.

HTH

Tim

 

[Guest]

Tim

Thanks for your help and time - absolutely spot on and just what I needed!

--
Zani
(if I have posted here, I really am stuck!)

Zani,

You can use the calculate event to detemine when each sheet was last
calculated - e.g. the following puts the date & time on sheet that was
re-calculated

This one in the 'ThisWorkbook' module to capture all re-calculations:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Range("A1") = Format(Now(), "dd mmm yy")
Sh.Range("A2") = Format(Now(), "hh:mm")
End Sub

Or this one in the Sheet module to capture re-calculations on that sheet
only:

Private Sub Worksheet_Calculate()
Me.Range("A1") = Format(Now(), "dd mmm yy")
Me.Range("A2") = Format(Now(), "hh:mm")
End Sub

You could also use the Workbook_SheetChange or the Worksheet_Change events
to get the time of the last modification.

HTH

Tim