system time stamp a cell when change in target cell. ( note target cell is formula ref)

Hi All

I got a task at hand to automate a dashboard

i need to auto timestamp using system time a particular cell based on any change in the target cell.
have used the below vba code successfully.
Private Sub Worksheet_Change(ByVal Target As Range)

Now going one step further, when i make the target cell formula based i.e (derive its value from some other sheet in the work book)
the below VBA code doesnt work. it works only when i manually change the value in the target cell as against putting a formula and deriving it

Please Login or Register  to view this content.

Will appreciate any quick help on this one.

Moderators Note: Please follow Forum Rule #3 and use code tags. Added this time, but please use them in the future…Thanks.

Originally Posted by R_VBA

...when i make the target cell formula based i.e (derive its value from some other sheet in the work book)
the below VBA code doesnt work

Your choices are either

a) use a Calculate driven event
or
b) move your Change event to the precedent sheet object or Workbook level event

Regards a) there is no Target in a Calculate event and Calculation can of course be triggered by a large number of Volatile actions - generally you would use a Static / cached variable (or store in hidden name) to identify as to whether or not the cell of interest has actually changed as a result of the calculation

Personally I would advocate b) - whether to use an alternatve sheet object or the Workbook level event would depend on the number of precedent sheets in question.

Below is one approach using the Workbook level event as an example... - let's assume for sake of example that the formula on your "Summary" sheet references Sheet1!C1 and Sheet2!E2

Please Login or Register  to view this content.

Of course if the immediate precedents are themselves formulae then you need to work back to the "prime mover" cells (or switch to a Calculate event driven approach).

if it's only one cell I would suggest using the calculate event as it will be more flexible if you need to alter the formula involved