MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel, I need a cell to update with the last day modified.


Posted by Tomber on August 20, 2001 9:31 AM

Excel, I need a cell to update with the last day modified. It can't just update when a sheet is saved it has to be when the sheet is actually modified. This is for ISO certification so other companies must have done it.


Posted by Damon Ostrander on August 20, 2001 12:27 PM

Hi Tomber,

If it is just one worksheet, I recommend using the worksheet's change event. For example, to keep the worksheets last change date-time in cell A1:

Private Sub Worksheet_Change(ByVal Target As Range)
[A1] = Now()
End Sub

If, on the other hand, you have multiple worksheets and you want to keep track of the date each has been changed, you can do this using the workbook's (ThisWorkbook object) SheetChange event. For example, this puts each sheet's last changed date-time in cell A1 of each sheet:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.[A1] = Now()
End Sub

Happy computing.

Damon