Forum Discussion
Date of Last Modification of a Worksheet
Hello all,
I have been trying to workshop a VBA code block with ChatGPT to display the date in a particular cell whenever ANY change is made in the worksheet. Here is my code so far:
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
' Get the last modified date
lastModified = Now
' Display the last modified date in the target cell
targetCell.Value = lastModified
Application.EnableEvents = True ' Re-enable events
End If
End Sub
However, my issue is that whenever I open said document (without any changes made in it), the date of last modification will update to that time. I would like it to stay as whatever date and time it had prior. Any suggestions?
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- Even when I open your example file, it updates to the opening time. Clearly, there's some process updating the sheet/a cell happening when I open any Excel file, but am unaware of it. Thanks for trying, though! I have no other VBA code running.
The code works as described for me.
It is not changed when the file is opened, only when the change is made.
Have you updated?
A repair for this?
If this doesn't help, disable all AddInn and try again.
If this does not help either, start in safe mode and then test the function.
If not, I'm at my wits end.
- The date and time should stay put. Is anything in your other VBA code making changes to the sheet when it is opened?
- I have no other VBA code running, so something has to be happening behind the scenes when I open a file that I'm unaware of.
I think this code does exactly the same as yours:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' Disable events to prevent recursive triggering ' Display the last modified date in the target cell Range("B5").Value = Now Application.EnableEvents = True ' Re-enable events End Sub
