Capture the date and time any cell in the workbook has been updated

**bigbavarian** · 04-09-2009, 12:10 PM

Hello all,

I am trying to create a cell on a worksheet that lists the date/time that any cell in the workbook has been updated.

Example, the summary tab would have a cell that says "Date Last Updated". The cell next to it would have the date some last updated a cell in the workbook.

Does anyone know if this is possible?

Stephen

**DonkeyOte** · 04-09-2009, 12:18 PM

You could use the Workbook level Worksheet_Change event if you wanted to/

Let's first name the cell in which the stamp will reside, let's call it: _lastupdate

Please Login or Register  to view this content.

The above should be placed within ThisWorkbook object.

**shg** · 04-09-2009, 12:32 PM

You can't test the intersection of ranges on different sheets without raising an error.

**DonkeyOte** · 04-09-2009, 12:41 PM

Thanks shg, I didn't test - my bad.

Is there an elegant way to test if Target is the Named Range ?

I could only think along lines of:

Please Login or Register  to view this content.

Which is cumbersome... must be a better way to get full address of Target ?

EDIT: Though I guess in this instance a simple On Error Resume Next prior to the test of Intersect would suffice given it must be ok to proceed...

**bigbavarian** · 04-09-2009, 12:44 PM

That did work well on the one sheet but I did get the error when entering a value on another tab like shg said.

Is there something that works for the whole workbook?

**DonkeyOte** · 04-09-2009, 12:45 PM

EDIT: revised... having a bad day... grr...

Please Login or Register  to view this content.

**bigbavarian** · 04-09-2009, 12:49 PM

Simply perfect. Thank you sooo much.

Stephen

**shg** · 04-09-2009, 12:55 PM

Do you need to test at all, DO? Why not just set the time in the range -- the only wrinkle is if the user overwrites the range, and then it just gets overwritten again.

**DonkeyOte** · 04-09-2009, 01:04 PM

I agree with you shg, I was just trying to be as "clean" as possible, ie separate out that cell such that it could be set manually if ever the need arose... I guess in reality it would be safer for that to not to be possible ... it would otherwise defeat the purpose of the entire exercise !

BB, general consensus is to remove the test altogether...