ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Last date edited - in a single cell to be updated every time a sheetis edited. (https://www.excelbanter.com/excel-worksheet-functions/454985-last-date-edited-single-cell-updated-every-time-sheetis-edited.html)

meleady420

Last date edited - in a single cell to be updated every time a sheetis edited.
 
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.


meleady420

Last date edited - in a single cell to be updated every time asheet is edited.
 
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!

Auric__

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.

meleady420

Last date edited - in a single cell to be updated every time asheet is edited.
 
On Thursday, 5 November 2020 at 17:41:40 UTC, Auric__ wrote:
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.

Thanks alot for your help but i cant get this working.
Any advice much appreciated.
- I am in a macro enabled workbook
- Alt F11
- Copy and past the test above
- Alt Q

Am i missing any steps?

I have tried then using Alt F8 and changed A1




Thibaud Taudin Chabot

Last date edited - in a single cell to be updated every time asheet is edited.
 
Op 5-11-2020 om 17:11 schreef meleady420:
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!


https://www.extendoffice.com/documen...timestamp.html

Auric__

Last date edited - in a single cell to be updated every time a sheet is edited.
 
meleady420 wrote:

[snip]
Thanks alot for your help but i cant get this working.
Any advice much appreciated.
- I am in a macro enabled workbook
- Alt F11
- Copy and past the test above
- Alt Q

Am i missing any steps?

I have tried then using Alt F8 and changed A1


* Did you allow macros to run? (Security settings might prevent them from
running.)

* Did you put it in the workbook's ThisWorkbook object? (It *must* go there.)

* Is there already a Sub Workbook_SheetChange? (You'll have to merge them
together.)

* Did you ensure you were using only one of my subs at a time? (It's one or
the other, not both.)

* Did you actually make a change in a worksheet? (It's not magic; the code
won't fire unless you change some data, not just formatting. Select a blank
cell and hit the Del key.)


If you answered "yes" to all of the above, then try the page that Thibaud
Taudin Chabot linked to. It's a method I'd never heard of before,
specifically ActiveWorkbook.BuiltinDocumentProperties("Last Save Time").

--
She had not thought these words as she saw the humans coming to kill,
but it was in words that Ender understood her.


All times are GMT +1. The time now is 12:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com