abort macro dialog box

[Chris]

Thanks for your verry quick respond.
I found somthing in the helpfile and made som buttons to it.
So this is the code i intergrated in it:

On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler

For X = 1 To 1000000 ' Do something 1,000,000 times (long!)
' do something here
Next X

handleCancel:
If Err = 18 Then
If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then
Exit Sub
Else
Resume
End If
End If


But thing i dont like when i say "NO" He start running the macro from
the beginning, and not from the point i interupt him.
Can this be doen or is that just impossible?

 

[Nick]

Chris

Your code is fine exept that you need to put Exit Sub just before your error
handler to stop the code running in to your error handler code as this can
cause some strange looping effects (from bitter experience)

The resume statement will carry on execution at the point the error was
thrown (i.e. when cancel is pressed) so I'm confused as to my it would start
from the beginning again. I ran your code and just added
application.statusbar = x in the Loop and when I press cancel it resumes at
the point I pressed cancel. Try adding this code and see what happens. If
you still getting the error then post your live code and I'll have a look at
it.

Nick

 

[Charlie]

Have you tried Resume Next statement?

 

[Charlie]

Another possibility is:

On Error Resume Next
Application.EnableCancelKey = xlErrorHandler

For X = 1 To 1000000 ' Do something 1,000,000 times (long!)
' do something here

If Err = 18 Then
If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then Exit Sub
Err = 0
End If

Next X

 

[Chris Nieboer]

Thanks for your help guys.

Since I have a protected sheet i made a 'goto' istead of 'exit sub'(in
the code you'll see why). If the problem is there can you please give me
the exact place where to put that sentence, i tried several places but
it won't work.
I have tried charlies code aswell but when i hit the 'Esc' button he
just stops and don't even come with the message box.

I have a progress bar in my macro, who start counting again, when I say
'NO'. So maybe it is just the progress bar who start couting again,
while the macro just go further..... I'll have to look at that, but i'm
verry new in VBA so that will take som time. When i found out whether it
is just only the progress bar or the whole macro i'll let you guys know.

Thanks so far.


*** Sent via Developersdex http://www.developersdex.com ***

 

[Chris]

Hey guys,

Again thanks for your help.
I just looked again to my macro and i made a verry stupid mistake. I had
paste the error handler in the commandbutton_sub instead of in the
running sub.



*** Sent via Developersdex http://www.developersdex.com ***