close application when interrupt by user

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How to close the application without saving the changes when user press
"Ctrl" and "Break" button to interrupt the macro during code running?
 
Terry,
Basically you can't. If the user has interrupted code, how are you going run
code ?

Maybe you need Application.EnableCancelKey

NickHK
 
I want to invoke some procedure to close the file when user do it.

Application.OnKey "^{BREAK}", "CloseFile"

Sub CloseFile()
ActiveWorkbook.Close savechanges = False
End Sub

But the problem is where I should put the code? as "on error" method will
keep monitor the errors when code running. hoe does "OnKey" works?
 
Terry,
I would be surprised if Excel can work like that, because if code is
executing (which must be the case if the user presses ^BREAK), your code
cannot run.
I suppose that's why your have the .EnableCancelKey property.
You can achieve your goal if you set it to = xlErrorHandler ?
See the help for an example.

NickHK
 
Hi Nick,

Can you expand on the .EnableCancelKey property, and the solution you gave
to Terry? This sounds like something I could really have a use for.

Thanks,

Alan
 
Alan,
Whilst I would not necessarily advise closing/quitting in such a suitable,
you can decide what to do:

Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long

Application.EnableCancelKey = xlErrorHandler
On Error GoTo Handler

For i = 1 To 100000
For j = 1 To 10000
Debug.Print "Value=: " & i * j
Next
DoEvents
Next

Exit Sub

Handler:
'Either an error or the code has been stopped
MsgBox "You are in the handler." & vbNewLine & "Exiting routine."

'Do what you want
End Sub

NickHK
 
Back
Top