View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Auric__ Auric__ is offline
external usenet poster
 
Posts: 538
Default 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.