VBA - Excel 文件无响应，不可选择
请帮助我解决以下问题，我已经竭尽全力解决这个问题，但我完全没有想法。
我们有一个巨大的宏，可以将表中的值恢复到文件中的不同工作表。用户批准/拒绝每个更改。 问题是，当我到达名为“国家/地区输入”的工作表时，Excel 进入锁定状态，它不响应任何宏或命令，您无法选择，无法再取消隐藏列或获取单元格属性（如果我尝试获取一个简单的 Interior.color，我得到 1004：无法获取 Range 的 Interior 属性）。我检查了工作簿是否已解锁，工作表是否已解锁，文件是否为只读，所有检查都正常，但宏不再影响文件。
有人遇到过这个吗？知道我应该寻找什么，或者如何防止它发生吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
它只在宏运行时执行此操作，还是在宏完成后执行此操作？
您是否修改了宏中的“Application.ScreenUpdating”？
如果是这样，您是否有另一个返回 ScreenUpdating = true 的宏，以防主宏崩溃？
如果没有这些情况，我同意FaneDuru，你可以单击编辑器屏幕的左侧放置一个小红点，然后宏代码将暂停并等待你按F8（前进一步）或F5（继续运行所有代码）。我们需要更多细节来提供进一步帮助。
（我本想将其添加为简单评论，但我刚刚加入，不具备对对话进行简单评论所需的声誉。抱歉！）
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!)
谢谢大家的想法，这对于找出真正的原因很有用。
只是为了做好准备，我一直在与我的经理交谈以清除旧代码，因为它实际上使用单元格更改触发器和工作表更改触发器以及您无法正确控制的触发器分配，但这不是新功能，因此它不会优先更新，直到客户开始抱怨，然后就紧急了......
无论如何，问题来自触发器，因为它们导致数据透视表更新，并且数据透视表与宏不同，有一个单独的计算线程，因此，在宏进入下一步之前，数据透视表不会完成更新，导致 Excel 文件进入这种奇怪的状态。我禁用了一些执行此操作的触发器，并且一切都运行得更好。
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.