I have a very unusual problem. My workbook is fairly substantial and uses a good amount of vba/vb to handle UI issues as well as perform some calculations via UDFs.
Recently I have found a situation where inter-sheet links stop working. The application allows the user to add cashflow engines - it does so by making a copy of two sheets (that are linked together - input/output and the engine). One of my users has taken to moving one of the sheets after they have been created (dragging and dropping in a new location). After this moving of the sheet, then when a change is made to any sheet, cells on the sheet recalculate, but cells on other sheets that refer to that cell do not recalculate. After a Cntl-Alt-F9 the workbook is functioning again until the user moves a sheet - any sheet. When I look at the cell's dependents, they show up after the rebuild, but only local ones show up after the sheet move. Oddly, this does not happen after the first create/move, but only subsequent create/moves.
I have checked my App_Event routines, and none are firing when the move happens - although there is some activity as indicated by the top bar of the vbaide window. Not sure what activity would be needed for just a sheet move, but there is something, but it does not appear to be coming from my vba routines.
Making things worse, the sheet move does not trigger any sheet events other than a sheet_calculate event (for every sheet) which seems odd since nothing really has changed - the sheet name has not changed, nor its codename - curious
The only solution I can think of is to trap the sheet_change event then compare the position of all sheets and if changed force a rebuild - but the comparison would be slow and would have to be done for every cell change in the workbook.
Any insight and suggestions are welcome - solutions would be really appreciated - I am stumped
Thanks