Update TimeStamp when a range changes

[David Evans]

I've tried to re-create a sheet in the attached file, that *used* to work, but the pilot of this craft obviously fell asleep for a while .... and is now lost

My intent is to have the datestamp update itself when the range (Rng.GrandTotalFees - yellow) changes ....

The current technique is one I found out there, but I have mislaid the source .. anyone here a bit of a wizard on these Worksheet_ actions gizmos ...

 

[NARAYANK991]

Hi David ,

A few clarifications are needed :

The named range is 8 cells , while the timestamp cell is just 1 ; does this mean that if any of the cells A5: H5 is changed , the timestamp cell C1 should be updated ?

Secondly , the 8 cell range does not contain anything at present ; is it going to be changed by data entry by the user , or will it contain a formula which will change based on data entry in other cells ?

If it is the latter , then a Worksheet_Change event procedure cannot be used , since a change in cell contents through a formula does not trigger a Worksheet_Change event ; instead a Worksheet_Calculate event procedure can be used , but this will be triggered for any recalculation anywhere in the workbook.

Narayan

 

[David Evans]

Hi Narayan -

Thanks for looking at this. To answer your questions

1. Any change in the names range A5:H5 prompts a change in the C1
2. The 8 cell range is formula based - the user does not touch it.
3. I did change the code in the worksheet uploaded back to worksheet-calculate and it seems to be working.

However, do you have a better way?

 

[NARAYANK991]

Hi David ,

No ; if the formulae in the 8 cell range are simple , it may be possible to use a Worksheet_Change event procedure on the precedent cells ; if your workbook is not very compute-intensive , you can stick with the Worksheet_Calculate event procedure.

Narayan

 

[David Evans]

Hi David ,

No ; if the formulae in the 8 cell range are simple , it may be possible to use a Worksheet_Change event procedure on the precedent cells ; if your workbook is not very compute-intensive , you can stick with the Worksheet_Calculate event procedure.

Narayan

Narayan - I'm back! Went off developing something wholly different only to return to this ....
To answer your original question, I have a 75 cells range that each contains a simple formula. Worksheet_Calculate appears to be the closest thing I've come to a perpetual-motion machine - it is constantly calculating in what appears to be an endless loop. Should I be using Workbook_SheetChange on a few specific ranges? What needs to be changed in these ranges to trigger the event? that is where I'm not understanding the concept correctly, I *think*

Thanks for your insight and Wisdom (and of course, anyone else who wants to participate in my edification is also gratefully thanked!)

D

 

[NARAYANK991]

Hi David ,

I forgot to mention the most important point about event procedures , the possibility of their being triggered from within the event procedure itself.

To prevent this , ensure that every event procedure has the following statement somewhere within it ; the location will be decided on the basis of what the event procedure is doing ; thus a Worksheet_Change event procedure should have the statement just before it makes any change to a worksheet which might trigger the Worksheet_Change event again.

Application.EnableEvents = False

Then , before you exit the event procedure , you need to ensure that the following statement is executed :

Application.EnableEvents = True

These two statements are absolutely essential , unless you are clear about why you want application events to remain enabled within the event procedure.

Narayan

 

[David Evans]

Thanks, Narayan - will put it to the test tomorrow morning!
Appreciate your willingness to share your knowledge