close application when interrupt by user

[Guest]

How to close the application without saving the changes when user press
"Ctrl" and "Break" button to interrupt the macro during code running?

 

[NickHK]

Terry,
Basically you can't. If the user has interrupted code, how are you going run
code ?

Maybe you need Application.EnableCancelKey

NickHK

 

[Guest]

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?

 

[NickHK]

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

 

[Guest]

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

 

[NickHK]

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