Stopping the user from using Crtl+Break

[Rasm]

I found this code on the web - it stops the user from crashing the program by using Ctrl+break

It seems like a good idea - but I am wondering if this is a good concept - any comments would be appreciated.

[FONT=Lucida Console]Sub another_code_that_runs_5_seconds()[/FONT]
[FONT=Lucida Console]On Error GoTo MyErrorHandler:[/FONT]
[FONT=Lucida Console]t = Timer[/FONT]
[FONT=Lucida Console]Application.EnableCancelKey = xlErrorHandler[/FONT]
 
[FONT=Lucida Console]Do While Timer - t < 5[/FONT]
[FONT=Lucida Console]Loop[/FONT]
[FONT=Lucida Console]MsgBox 1[/FONT]
[FONT=Lucida Console]Application.EnableCancelKey = xlInterrupt[/FONT]
 
[FONT=Lucida Console]Do While Timer - t < 10[/FONT]
[FONT=Lucida Console]Loop[/FONT]
 
[FONT=Lucida Console]MyErrorHandler:[/FONT]
[FONT=Lucida Console]If Err.Number = 18 Then[/FONT]
[FONT=Lucida Console]  MsgBox "Stop hitting ctrl + break"[/FONT]
[FONT=Lucida Console]  Resume[/FONT]
[FONT=Lucida Console]Else[/FONT]
[FONT=Lucida Console]  'Do something to make your impatient user happy[/FONT]
[FONT=Lucida Console]End If[/FONT]
[FONT=Lucida Console]End Sub[/FONT]

 

[Leith Ross]

Hello RASM,

Personally, I try to avoid disabling any critical application or system components at all costs. If something goes wrong either due to user intervention or at the system level, you can only stop the macro by killing the Excel process and lose all your data. Not a particularly sound option.

If the macro takes a long time to complete then a better option would be a progress bar. The user would then know that the macro is running and not hung.

Sincerely,
Leith Ross

 

[Rasm]

I hear you load & Clear - thats why I asked - just userforms will show not responding - unless you have some dummy code that toggles some value in a sheet somewhere. 2ndly there is no progress bar just the statusbar - so I show a counter in my statusbar.
Thanks for your comments - I think I will stay away from blocking the user

 

[ZVI]

The internal VBA message "Code execution has been interrupted" [Continue] [End] [Debug] [Help] after Ctrl-Break pressing is not user friendly for the one who are not familiar with VBA.
But this message can be replaced via EnableCancelKey by more informative for the users of your code.

Instead of blocking the user it is desirable to give them possibility to interrupt the durable code.
The code below demonstrates such possibility.

Sub Test()
  
  On Error GoTo handleCancel
  Application.EnableCancelKey = xlErrorHandler
  
  MsgBox "This may take a long time: press ESC to cancel"
  
  Dim t!: t = Timer + 10
  While Timer < t
  Wend

handleCancel:
  If Err = 18 Then
    MsgBox "You cancelled"
  Else
    MsgBox "Done"
  End If
  
End Sub

 

[Rasm]

ahhh - I see what you are saying - tell the user that the program is actually busy and for him to wait - press cancel - or give him choice of stopping. That makes sense - now I may change my mind - if I should or should not use this code - thanks ZVI