Please help me with the following issue, i've been throwing everithing i've got at this, and i'm all out of ideas.
We have a huge macro that restores values from a table to diferent sheets in the file. The user approves/rejects each change. The problem is, when I reach a sheet called "Country Inputs", Excel goes into a locked state, where it does not respond to any macros or commands, you cannot select, you can no longer unhide columns or get cell properties (if i try to get a simple interior.color, I get 1004: unable to get interior property of Range). I checked if workbook is unlocked, worksheet is unlocked, if file is read only, all checks are ok, but macros can no longer affect the file.
Has anyone ran into this? any idea what I should look for, or how to prevent it from happening?
Thank you everyone for your ideas, this was usefull in finding out the real cause.
Just to set the stage, I've been talking with my manager to clear the old code, because it's actually using cell change triggers and sheet change triggers and allot of trigger that you cannot controll properly, but it's not new functionality so it does not have priority for updates, untill clients start complaning and then it's urgent ...
Anyway, the issue was coming from the triggers, as they were causing a Pivot table to update, and the pivot table, unlike macros, has a separate calculatin thread, so the pivot table would not finis updating before the macros go to the next step, causing the Excel file to go into this weird state. I disabled some of the triggers that did that, and no everithing runs better.