Excel worksheet last modifed 1

[WingandaPrayer]

Hi,

How do I show in a cell the last date and time a individual worksheet was updated ?


thanks



Seasons greetings

 

[SkipVought]

Hi,

There's no built-in functionality in Excel to do what you want.

However, you can write VBA code to do that, using the Worksheet_Change event. Triggering on a CHANGE event, however, requires some level of skill, since the DATE that you will be placing on the sheet is ALSO a change, that COULD trigger the code to execute endlessly.

Please explain in a bit more detail what you want to do under what circumstances. Do you want to do this for only one worksheet or all worksheets? Do you want this result on the SAME sheet or a separate sheet?

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[WingandaPrayer]

Hi Skip,

The date & time to appear on each individual worksheet within the the spreadsheet when the individual worksheet has the information modified(added or removed) rather than viewed.

e.g. A spreadsheet with five worksheets could show five different modified dates & times.Each date & time only to show in the individual sheet.

thanks



Need to check my replies address!

 

[SkipVought]

use the Workbook_SheetChange event in the ThisWorkbook Object that you can view in the Project Explorer in the VB Editor (alt+F11)

Let's assume that A1 is the cell that will contain the timestamp.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Address <> "$A$1" then Sh.[A1].Value = Now
End Sub

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[WingandaPrayer]

Hi Skip,

I think I'm missing something, as usual!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Address <> "$A$1" then Sh.[A1].Value = Now
End Sub

Should I be showing the

range
and change Target.Address to Book1


cheers

 

[SkipVought]

What is Book1?

I thought that you had workbbok with worksheets that needed a data/time stamp whenever a change was made.

As I stated, "use the Workbook_SheetChange event in the ThisWorkbook Object..."

However, if you are opening some OTHER workbook, all bets are off. Please be clear, concise and COMPLETE in your requirements.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[WingandaPrayer]

Hi Skip,


As I wasn't at work I couldn't try this on the spreadsheet I wanted to.

I tried on a spreadsheet at home, couldn't get the date/time stamp to appear so I thought "try from scratch"(Book1) that's why I was querying range and Target address originally.

thanks

 

[WingandaPrayer]

Just realised what I was doing.

I loaded into Sheet 1 rather than This Workbook.


thanks again


All bets on