Forum Discussion

dborg1330's avatar
dborg1330
Copper Contributor
Apr 11, 2024

Last Modified Date for anything CHANGED within a sheet (VBA)

Hello,

 

I have a workbook with about 60 individual sheets, 10 of which are "overview" sheets returning specific data from the other 50 sheets.  I would like one cell in each of the 50 sheets to display the date and time that the last CHANGE was made to that sheet, not last date opened, printed or saved. I would like it to update the moment any cell within the sheet has been altered.

 

Is this possible

Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 11, 2024

    dborg1330 

    To avoid individual procedures for each of the 50 sheets, create an event procedure in the ThisWorkbook module.

    This example sets the date and time in Z1. You can easily specify another cell in the code.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        ' Skip the overview sheets
        Case "Overview1", "Overview2" ' ...
            ' Skip these
        Case Else
            Application.EnableEvents = False
            Sh.Range("Z1").Value = Now
            Application.EnableEvents = True
    End Select
End Sub

Resources