VBA Timestamp only updates if Target cell is manually changed?

[arwebb0264]

Hi all,

My primary worksheet (Sheet 2) contains a table that updates based on changes to the referent cells on Sheet 4.

I want to make VBA timestamp my table on Sheet 2 anytime the total changes, i.e. when the Sheet 4 referents make Sheet 2 change.

I am using the following code now:

Please Login or Register  to view this content.

The problem I'm having is that this timestamp macro only works if I *MANUALLY* edit the contents of the target cell (the 'total' cell from Sheet 2's table). So I get a timestamp if I manually change the target. But I want a timestamp automatically when the target cell is changed by changes in Sheet 4.

Any ideas guys? Thank you!

[jason.b75]

Code tag request removed.

[arwebb0264]

I apologize. Done.

[jason.b75]

Set the target cell as the one in sheet 4 that will be changed manually, not the one in sheet 2 that is changed by the formula (you will need to enter the code into the sheet 4 module, not the sheet 2 module).

There is no ideal way to track changes to formula in specific cells, it's easier to track the cells that cause the formula to change, although this is not always ideal.

[arwebb0264]

Jason,

Thank you for the advice - my question is how to set a target cell that is in a different worksheet from the VBA script.

So, I entered my code into the Sheet2 VBA window, but I want to take your advice and set the target cell from Sheet 4.

I am sorry if I am explaining poorly, but I believe I am following you.

[arwebb0264]

Set the target cell as the one in sheet 4 that will be changed manually, not the one in sheet 2 that is changed by the formula (you will need to enter the code into the sheet 4 module, not the sheet 2 module).

There is no ideal way to track changes to formula in specific cells, it's easier to track the cells that cause the formula to change, although this is not always ideal.

Jason - just to be more specific:

I am asking whether I should:

• enter the VBA code in the VBA window for Sheet 4, where the formula is calculated, and tell VBA to timestamp in Sheet 2.
• enter the VBA code in the VBA window for Sheet 2, where I want the timestamp to appear, and tell VBA to target a cell in Sheet 4.

Thank you. I apologize for such novice confusion.

[jason.b75]

No need to apologise, everybody has to start somewhere.

Try this in the sheet 4 code module.

Please Login or Register  to view this content.

This will (should) timestamp Sheet2 A37 when Sheet4 B47 is changed.

[arwebb0264]

No timestamp.

I pasted into Sheet 4 Code window - is this the "module"? I know that I can Insert > Module in the left-hand nav pane, too.

[jason.b75]

That is the correct module, modules that you add by using Insert > Module don't work with events (code that runs automatically when a specific action is taken).

Possibly events have been disabled, to re-enable them,

In the code editor, press Ctrl g

Then in the panel that opens at the bottom, enter the following line.

Please Login or Register  to view this content.

and press enter.