Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 5th 20, 04:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 3
Default 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.


  #2   Report Post  
Old November 5th 20, 05:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 3
Default 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!
  #3   Report Post  
Old November 5th 20, 06:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 532
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.
  #4   Report Post  
Old November 9th 20, 09:44 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 3
Default 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



  #5   Report Post  
Old November 9th 20, 03:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 2
Default 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


  #6   Report Post  
Old November 9th 20, 08:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 532
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
set up a shared workbook to be view/edited at the same time? mario frias Excel Discussion (Misc queries) 1 July 15th 08 04:50 PM
Automatically pu date when cell is edited Haris Excel Worksheet Functions 3 October 30th 07 07:32 AM
Recording the date another cell is edited or modified. Ed Excel Worksheet Functions 2 December 2nd 06 04:22 AM
update a date cell only when worksheet is edited Joe Black Excel Discussion (Misc queries) 1 September 23rd 05 05:11 AM
Date last edited Mike Sharp Excel Discussion (Misc queries) 1 May 18th 05 06:08 PM


All times are GMT +1. The time now is 09:49 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017