Last date edited - in a single cell to be updated every time a sheet is edited.
meleady420 wrote:
On Thursday, 5 November 2020 at 15:31:17 UTC, meleady420 wrote:
Hi,
I have a workbook with multiple sheets.
I need an automatically updated cell that updates every time the sheet
is edited and I will need this on multiple tabs that update
individually only when that specific tab is updated.
"Last updated: DD MM YY"
Thankyou in advance for any help.
Automatically updates with the date!
You'll likely need to script it with VBA. Put this in the workbook's
ThisWorkbook object:
Private changed As Boolean
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If changed Then Exit Sub
changed = True
Sh.Range("A1").Value = "Last updated: " & IIf(Day(Now) < 10, "0", "") _
& Day(Now) & IIf(Month(Now) < 10, " 0", " ") & _
Month(Now) & " " & Right(Year(Now), 2)
changed = False
End Sub
Replace "A1" with the address you want the date at, and bear in mind that it
will be the same location on each sheet.
This will only mark a sheet as updated if it is the updated page, resulting
in different dates on each sheet. If you need the date to be the same on
each sheet, use this instead, still in ThisWorkbook:
Private changed As Boolean
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If changed Then Exit Sub
Dim x As Worksheet
changed = True
For Each x In Sheets
x.Range("A1").Value = "Last updated: " & IIf(Day(Now) < 10, "0", "") _
& Day(Now) & IIf(Month(Now) < 10, " 0", " ") & _
Month(Now) & " " & Right(Year(Now), 2)
Next x
changed = False
End Sub
--
Between two evils, I choose the competent one.
|