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?

CodePudding user response：

Does it only do this While a macro is running, or even after it has finished? Have you modified "Application.ScreenUpdating" in the macro? If so, do you have another macro that returns ScreenUpdating = true in case the main macro crashes?

In the absence of those situations, I agree with FaneDuru, you can click the lefthand side of the editor screen to place a little red dot, the macro code will then pause and wait for you to press F8 (one step forward) or F5 (continue running all code). We need some more details to help further.

(I would have added this as a simple comment, but I just joined and don't have the required reputation to simple comment on a conversation. Sorry!)

CodePudding user response：

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.