Date of Last Modification of a Worksheet

awbenson 

Attached is a suggested solution, example file included.

The file can be opened as many times as you like and nothing changes in cell B5.

However, if any cell has been changed in text or formula, the date will also change.

Think this is in your mind, as far as I could gather from the text :).

Private initialOpenDate As Date

Private Sub Workbook_Open()
    initialOpenDate = Now ' Record the initial open date and time
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastModified As Date
    Dim targetCell As Range
    
    ' Set the target cell to the desired cell where you want to display the last modified date
    Set targetCell = Range("B5")
    
    ' Only update if any cell within the worksheet is changed
    If Not Intersect(Target, Me.UsedRange) Is Nothing Then
        Application.EnableEvents = False ' Disable events to prevent recursive triggering
        
        ' Check if any changes have been made since the initial open date
        If Now > initialOpenDate Then
            ' Get the last modified date
            lastModified = Now
        Else
            ' No changes have been made, so keep the initial open date as the last modified date
            lastModified = initialOpenDate
        End If
        
        ' Display the last modified date in the target cell
        targetCell.Value = lastModified
        
        Application.EnableEvents = True ' Re-enable events
    End If
End Sub