Per and Jim,

Thanks both for testing.
I was kind of expecting one or two might replicate, perhaps it's only in my
systems. Actually no, the issue arose from someone else experiencing similar
in this ng several months ago.

Jim,
My rule #3: don't run long loops without a DoEvents inside the loop.

Really? Well an occasional DoEvents indeed prevents the problem (if done in
time), as does intermittent re-calc. As it happens MS have also suggested
DoEvents. What I find curious is if anything I would expect the scenario
more likely to occur with automatic calculation rather than manual.

However I don't think DoEvents is a good idea at all, at least not without
knowing the potential consequenses. Say the routine was called from a
button, user presses the button another 5 times whilst running between
subseqent Doevents (people do that kind of thing), when done the macro will
re-run another 5 times. Or maybe something else will get triggered. Also,
if DoEvents is called in every loop, as some do, it'll significantly slow
things.

Rules #1 & 2, dare I ask ? :-)

Regards,
Peter T

Peter,
Since you asked... <g
Rule #1 - Don't vote, it only encourages them.
Rule #2 - Get the money in advance.

DoEvents allows the user to quit running code with Escape or Ctrl + Break.
Also, long loops should have a progress indicator and that can provide
an interval for the DoEvents function.

I am unsure about multiple button clicks running code multiple times?
However, a lot of my code is run from MenuItems (Microsoft please note) or
a new sheet is activated to display results (no buttons on it).
Regards,
Jim Cone



"Peter T"
<peter_t@discussions
wrote in message
Per and Jim,
Thanks both for testing.
I was kind of expecting one or two might replicate, perhaps it's only in my
systems. Actually no, the issue arose from someone else experiencing similar
in this ng several months ago.

Jim,
My rule #3: don't run long loops without a DoEvents inside the loop.

Really? Well an occasional DoEvents indeed prevents the problem (if done in
time), as does intermittent re-calc. As it happens MS have also suggested
DoEvents. What I find curious is if anything I would expect the scenario
more likely to occur with automatic calculation rather than manual.

However I don't think DoEvents is a good idea at all, at least not without
knowing the potential consequenses. Say the routine was called from a
button, user presses the button another 5 times whilst running between
subseqent Doevents (people do that kind of thing), when done the macro will
re-run another 5 times. Or maybe something else will get triggered. Also,
if DoEvents is called in every loop, as some do, it'll significantly slow
things.
Rules #1 & 2, dare I ask ? :-)
Regards,
Peter T

Hi Jim,

Since you asked... <g
Rule #1 - Don't vote, it only encourages them.
Rule #2 - Get the money in advance.

Can't argue with that, so maybe #3 is right too

Regards,
Peter T