Forum Discussion
Excel automatically undid changes
JS4269 that's an interesting observation, I've only had it in the context of a circular reference on a particular operation and I now try desperately to avoid such references and immediately correct them if they should occur.
I do hope that Microsoft are listening on this as your experience sheds new light on this old problem, which certainly deserves their urgent attention as it's devastating to lose all your work.
Here's a trigger I found for the unintentional roll back of all changes. I can recreate the problem using this trigger once the problem has occurred for some reason. However, after a reboot, this problem trigger doesn't work again until the roll back changes problem happens for some other reason. Here is the sequence:
- Select some cells and Ctrl-C to copy
- Move cursor to upper left cell in desired paste location
- Right click mouse and move cursor to the right click menu paste options (My usual choice here would be to paste formulas so as to preserve formatting).
Excel then attempts to execute the various paste options (without a mouse click, i.e., the paste preview Excel does) as I move to the paste formulas option. Usually this works ok, but when the problem occurs, Excel appears to hang at one of the previews, and will not paste the formulas when I click the mouse. It then starts rolling back all changes. Thinking back, this series of operator interface is a trigger for all events of this problem that I can recall. I played with this a little bit and found I could fairly reliably recreate the problem using this sequence once the problem has occurred. Unfortunately, I could no longer trigger the problem after a reboot.
It appears Excel is getting lost when changing the paste previews rapidly, perhaps aborting a preview to start the next preview as the mouse is moved (the recalc time of the spreadsheet is not short). Regardless of the change roll back problem, Excel has an obvious bug in this paste preview code as it acts very quirky in this scenario even if it doesn't roll back changes.
One of the quirks which always occurs in this scenario is Excel partially changes from select cells to select objects. Excel will stop highlighting the selected cell, but the formula bar will update as the selected cell is changed, even though it is not highlighted. F2 will enter edit mode normally. To get the selected cell to highlight again, I have to turn on select objects and then turn it off - this restores normal cell selection highlighting.
I refer to my computer doing a 'time warp'! 😛 When I chose Paste Values, it starts processing the last 100 undos across multiple sheets. This has ruined my work day many times! There is no way I can remember all the work I've been doing for the last hour+. It's absolutely a Microsoft/Excel glitch. Why isn't it fixed?? I see now it's been issues for years! My solution is to change the undo levels in the register editor to a reasonable #, like 5. Why is the default 100??? Who didn't catch their error they did 100 actions back? Can we make the default 10 please?
Try searching for "circular reference" error in any of the worksheet. It is usually the culprit. TheSupraChris
