return last change date in a cell 1

[sophy]

Hi all --

I am wondering if it is possible to return the last change date to a worksheet in a cell in that worksheet. Thanks!

Cheers.

 

[shelron]

WORKSHEET change event will do it. Run it with a now function.

 

[Zathras]

If you mean the last date and time the workbook was saved, you can use a VBA function that fires when the workbook is opened.

Here is one way:

First create a range name LastSavedDateTime for one cell in one sheet. Then put the following macro code in the "ThisWorkbook" section.

Option Explicit
Private Sub Workbook_Open()
Dim i As Integer
  With Application.FileSearch
    .LookIn = CurDir
    .FileType = msoFileTypeExcelWorkbooks
    .FileName = ThisWorkbook.FullName
    .Execute
    For i = 1 To .FoundFiles.Count
      If UCase(.FoundFiles.Item(i)) = UCase(ThisWorkbook.FullName) Then
        Range("LastSavedDateTime").Value = FileDateTime(.FoundFiles.Item(i))
      End If
    Next i
  End With
End Sub

Now save the worksheet. The next time it is opened, the cell named "LastSavedDateTime" will have the date and time of the last save.

 

[sophy]

Thanks all! I used the macro and it worked like a dream.