Run Macro when cell contents change

[C J W]

Hi All

I'm after some help on the following please!

I have a macro which when run checks the contents of the active cell and if the contents matches either "Full Day" or "Half Day" the it will put a value from another cell in the adjacent column. The macro works great when running it direct.

When I add the code to the sheet change event I keep getting a message that I have run out of resources or excel crashes. I have one sheet change event which records any amendments to the sheet with the code located in the workbook object which I think might be causing the problem, but have no idea how to resolve it (And I need to keep this code). I have attached a sample of my workbook.

Thanks in advance for any help.

[cytop]

Workbook not downloaded...

The error occurs because changing a cell (either interactively or by code) will trigger a Worksheet_Change event. If you are already running code in a Change event then that basically causes a 'Circular Reference' (to use a formula analogy) in code.

The code changes a cell which raises a Change event which changes a cell which raises a Change event which changes a cell which raise... and so on. Eventually (in less than a second) Excel dies.

To avoid this, you NEED to turn off event handling so the change to the cell does not raise another event. This is done simply by using

Please Login or Register  to view this content.

but you MUST make absolutely sure that any errors are trapped correctly so you can turn Event handling on again, otherwise Excel will be totally unresponsive.

Please Login or Register  to view this content.

[C J W]

many thanks for the tip, works great now.