Hi all;
I inherited an Inventory file that occasionally displays bad data that cannot be attributed to bad counting. I haven't been able to duplicate the error or discover an error in the code. The code is a mess with few comments. I have a hunch that the owner of the file is the original programmer and she doesn't want to admit it. I also have a hunch that she is entering data with EnableEvents turned off so the Worksheet_Change event is not updating the figures, but she denies that.

So I've decided to try and move the macros into the Worksheet_Calculate event. Instead of using the Worksheet_Change event to calculate what the new figures should be, use hidden formulas to calculate what the figures should be and have the Worksheet_Calculate recognize that a change has been made and just copy the calculations from the hidden formulas into the data entry cells. I also want to put some kind of notice on the screen to let her know that EnableEvents needs to be turned back on so the Worksheet_Calculate can correct the display.

Any suggestions for all or part of the solution would be greatly appreciated.

The situation:
She keeps about 50 different products in her spare bedroom. Every week she transfers product to several different locations to replenish the stock there. Among other things, the file keeps track of how much product is at her "warehouse" and each location.

What she would like to see:
1) On Feb 8 she selects Location #1 and the screen shows that she transferred 5 units of Product #1 on Feb 1.
Please Login or Register  to view this content.
2) She runs a macro that fills in the new date and calculates how many units should be transferred (3) and the Worksheet_Calculate updates the Warehouse.
Please Login or Register  to view this content.
3) For some reason she decides to only send 2 units, so she manually replaces the 3 with a 2 (hence no formulas in Transfer column) and the Worksheet_Calculate event recognizes that the Warehouse needs to be updated.
Please Login or Register  to view this content.
4) While packing the transfer she discovers that she has 11 units in her "warehouse" so she manually replaces the 10 with an 11 (hence no formulas in Warehouse column).
Please Login or Register  to view this content.
5) On Feb 9 she selects Location #2 and it shows that she transferred 7 units to Location #2 on Feb 2.
Please Login or Register  to view this content.
6) She runs the macro that tells her to transfer 1 unit on Feb 9.
Please Login or Register  to view this content.
Some issues:
In Steps 2 & 3 the Worksheet_Calculate event recognizes that the Transfer column has been changed so it updates the Warehouse column.
But on Step 5, it needs to recognize that the "7" units was retrieved from a data storage sheet and doesn't need to update the Warehouse column.

If EnableEvents is turned off then:
In Step 2 the conditional formatting would recognize that the Transfer column has been changed and highlight the row to warn her that EnableEvents is off and the Worksheet_Calculate would update the Warehouse when EnableEvents is turned back on.
But in Step 4 the conditional formatting would recognize that the Warehouse column has been changed (not the Transfer) so the row is not highlighted. The Worksheet_Calculate would change the hidden formula to show 11 units after EnableEvents is turned back on.