Chengooi wrote:Some spreadsheets with no macro but with lots of TM1 link formulas on it, when the spreadsheet get refreshed, the code pop up and the debug tab was grey out. leaving continue and end tab.
The code doesn't have to be within the workbook itself. The tm1p.xla add-in has VB/A code in it as well, and that's most likely to be where the code interruption occurred rather than in the workbook. The fact that the [Debug] option is greyed out is indicative of this. It doesn't necessarily mean that it IS the tm1p.xla code that's been interrupted, but it does generally mean that it's happening inside a protected VBA project. When you're working with TM1 files, the tm1p.xla file is odds on to be the one.
VBA code can be interrupted by the user hitting [Ctrl] + [Break] or [Esc] unless the code writer has prevented that from happening by assigning the relevant value to the EnableCancelKey property. I don't know whether the Applix coders did that, but probably not. In which case, slap your users' hands if they go anywhere near the keyboard while TM1 is doing its thing.
Chengooi wrote:To get out from it user simply select end
[Continue] may get you a better result, though sometimes not. As a rule of thumb, though, you can't expect to [End] VB/A code and live happily ever after by doing so.
Chengooi wrote:but it did not actually fixed it as it pop up again and again until the sheet was closed off and at times restrat excel again and sometimes worse required to re-start computer to get rid of it.
Has anyone got any clue why and how to fix it?
Yeah, that can happen sometimes. Other than avoiding pounding the keyboard while a process is running (see above), I don't think that there's any guaranteed way of avoiding it. I had an instance the other day where some custom VBA code stopped because of a known bug in Excel. (The "limit to the number of times you can copy a worksheet" one discussed in
http://support.microsoft.com/kb/210684/en-us, the "workaround" for which, incidentally, does NOT work and which they STILL haven't fixed in Excel 2007.) Although the error was handled and exited the code correctly, thereafter whenever I tried to run the code I got the "code has been interrupted" error. Indeed, when I shut down Excel and restarted it, every add-in which ran initialisation code would trigger the same thing. This suggests that something went funky in the VBA library code that was running in the computer's brain, and a restart was the only solution.
Unfortunately the only option is to live with it. I don't find it happening frequently; maybe once every few months.
