Preventing user interruption of VBA macros - Interactive not working??

[tmktech]

I sure could use come help with this!!!!

I am not having success with "Application.Interactive = False" as a means to prevent Excel VBA code from being interrupted by the user (i.e. {ESC}) . I've also used the .Onkey method to set the {ESC} key procedure to null.

This occurs in both 2000 and XP.

I have looked for other actions which may be implicitly resetting the .Interactive method back to true, but have not identified any.

Is there a (better) way to acheive this?

Thanks in advance for any help!

 

[JustinEzequiel]

I only have Excel 97 so YMMV...

EnableCancelKey Property

Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD) user interruptions to the running procedure. Read/write Long.
Can be one of the following XlEnableCancelKey constants.

Constant Meaning
xlDisabled Cancel key trapping is completely disabled.
xlInterrupt The current procedure is interrupted, and the user can debug or end the procedure.
xlErrorHandler The interrupt is sent to the running procedure as an error, trappable by an error handler set up with an On Error GoTo statement. The trappable error code is 18.
Remarks

Use this property very carefully. If you use xlDisabled, there's no way to interrupt a runaway loop or other non – self-terminating code. Likewise, if you use xlErrorHandler but your error handler always returns using the Resume statement, there's no way to stop runaway code.
The EnableCancelKey property is always reset to xlInterrupt whenever Microsoft Excel returns to the idle state and there's no code running. To trap or disable cancellation in your procedure, you must explicitly change the EnableCancelKey property every time the procedure is called.

 

[tmktech]

Thanks! I'll try this out and get back to you. This may also be the answer I needed for a controlled clean stop of a process (by looking for the err.num = 18 and set a flag for a future stopping point).

All the best!

 

[tmktech]

Justin,

This works fine for processes that I want to run to uninterrupted to completion. Thanks.

However, I've got another scenario where I need to give the user the ability to bring an ongoing (potentially long) process to a controlled stop. If the ON ERROR permitted a GOSUB, I could test for ther err.num = 18, set a flag and RETURN, but the GOTO option for ON ERROR won't allow me to pick up where I left off.

Any suggestions??

 

[rmikesmith]

tmkconsultant,

You could set your procedure up along the following lines:

Sub YourSub()
Dim EarlyExit as Boolean


  On Error GoTo YourSub_Error

  Do
  ' Block of code that needs to complete to avoid problems
  If EarlyExit Then Exit Loop
  
  ' Block of code that needs to complete to avoid problems
  IF EarlyExit Then Exit Loop
  
  ' Additional code

  Loop Until...

YourSub_Exit:
  Exit Sub


YourSub_Error:
  If Err.Number = 18 Then
    EarlyExit = True
    Resume
  Else
    ' Error handling stuff for other errors
    Resume YourSub_Exit
  End If

End Sub

HTH
Mike

 

[tmktech]

Mike,

Thanks for sharing your knowledge! That was it. I can't believe I missed the return option on error handling (so much for MS VBA help).