Re: Error trapped only while stepping through the code - Not triggered when run

[Jim Cone]

Jeff,

A simpler code version might be the answer.
Give this a try...
'-------------------------------------------------
Sub RemoveSubtotals()
On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler

If ActiveSheet.ProtectContents Then
MsgBox "Please unprotect the sheet. ", vbInformation, _
" Remove Subtotals Program"
Exit Sub
End If

Application.StatusBar = "REMOVING SUBTOTALS..."
Application.ScreenUpdating = False
Selection.RemoveSubtotal
Application.StatusBar = False
Application.ScreenUpdating = True
Exit Sub

handleCancel:
Application.ScreenUpdating = True
Application.StatusBar = False
If Err.Number = 18 Then
MsgBox "User interrupt occurred. Program will close. ", _
vbExclamation, " Remove Subtotals Program"
Else
MsgBox "Error Number: " & Err.Number & vbCrLf & Err.Description, _
vbCritical, " Remove Subtotals Program"
End If

End Sub
'-----------------------------------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jeff" <jeffreywsmith@earthlink.net> wrote in message
news:1109613905.019388.231880@z14g2000cwz.googlegroups.com...
> Hi all,
>
> I have an xla application that includes a number of macros that I am
> trying to Error-proof. I am particularly interested in doing so
> because some other things I have going on in this app led me to employ
> some of Dave Peterson's LockWindowUpdate code (details in the code
> below). The first of the 3 macros below is an example of the macros I
> am trying to error-proof (& the only error I am really concerned with
> trapping is Error 18 which is supposed to be generated if the user
> cancels), the other two are supporting macros that might be invoked.
>
> I presently have coded for both Error 18 and Error 1004 (which is what
> actually gets generated when I [ESC] from this procedure when Removing
> Subtotals from a fairly large array of data). So the first of my two
> questions are:
>
> 1) Can anyone can shed any light on why Error 18 isn't generated when
> the user presses [ESC]?
>
> and, more importantly,
>
> 2) When I execute this Remove Subtotals macro by stepping through it
> (with the help of a Breakpoint), the MsgBox DOES display, allowing the
> user to respond; but when I just run it from a toolbar button and press
> [ESC] while the Selection.RemoveSubtotal is executing (again, on a
> large enough array of data to let you press [ESC]), the macro just ends
> WITHOUT displaying any MsgBox. Can anyone help me understand why this
> works when stepping through it but not when executed normally?
>
> Thanks!
>
> Jeff
>
> code follows:
- snip -

[Jeff]

Thanks for logging in on this, Jim. I tried your suggestion on an
unprotected sheet, and am still having the issue with the error
seemingly not getting trapped - NO message is displayed when I hit ESC
while Subtotals are being removed - the process just stops with a beep,
the Data Outline has been removed but the subtotals rows are still
there.

If it helps advance this issue, I would be glad to email a file I am
using with enough Subtotals to allow time to hit ESC (i.e., to allow
experimenting). I have been struggling with this issue for 2+ days ...
and just seem to be going around in circles ...

Jeff

[Jim Cone]

Hi Jeff,

You are right, the subtotaled data that I used to test is not
very large. It finishes removing before I can hit escape.

However, it appears to me that the problem? is probably intrinsic
to Excel. Analogous to calling a sub routine with its own error
trapping. Therefore I doubt if my experimenting would add anything.

I can think of a "kludge" that would check for any rows containing
"total" at the end of the sub, but there may be better approaches.
You could display a message box before starting the routine
and tell the user to wait for the process to complete.
You might even give them a time estimate based on the number
of rows in the subtotaled list.
My own preferred method would be to give the user some instructions
on how to do it himself... Data | Subtotals... | Remove All (button),
and put the burden back on Microsoft and the user for any use of
the escape key.

Regards,
Jim Cone
San Francisco, USA


"Jeff" <jeffreywsmith@earthlink.net> wrote in message
news:1109630115.545420.190820@g14g2000cwa.googlegroups.com...
> Thanks for logging in on this, Jim. I tried your suggestion on an
> unprotected sheet, and am still having the issue with the error
> seemingly not getting trapped - NO message is displayed when I hit ESC
> while Subtotals are being removed - the process just stops with a beep,
> the Data Outline has been removed but the subtotals rows are still
> there.
>
> If it helps advance this issue, I would be glad to email a file I am
> using with enough Subtotals to allow time to hit ESC (i.e., to allow
> experimenting). I have been struggling with this issue for 2+ days ...
> and just seem to be going around in circles ...
>
> Jeff
>

[Jeff]

Hi Jim,

I have thought of providing an appropriate message to the user as a
warning to not ESC before the procedure finishes, or to disable the ESC
key if pressed, but am really trying to understand/control error
trapping so I can also apply it to other macros in this app. There
seems to be plenty of examples of this on the internet where macros are
able to call their own error handling routines when ESC is invoked, but
I can't find anything that tells me why the code doesn't get triggered
here ... ! This app is all about saving time and unnecessary
mouse-clicks, so I wouldn't want to add any unnecessary delay/burden to
the user ... just trying to keep the user out of trouble here ...

Jeff

[Jim Cone]

Jeff,

The only thing that still might be worth a try is to add a "DoEvents" line...

Application.StatusBar = "REMOVING SUBTOTALS..."
Application.ScreenUpdating = False
Selection.RemoveSubtotal
DoEvents ' New code line
Application.StatusBar = False
Application.ScreenUpdating = True
Exit Sub

Regards,
Jim Cone
San Francisco, USA


"Jeff" <jeffreywsmith@earthlink.net> wrote in message
news:1109644182.895648.181370@g14g2000cwa.googlegroups.com...
> Hi Jim,
>
> I have thought of providing an appropriate message to the user as a
> warning to not ESC before the procedure finishes, or to disable the ESC
> key if pressed, but am really trying to understand/control error
> trapping so I can also apply it to other macros in this app. There
> seems to be plenty of examples of this on the internet where macros are
> able to call their own error handling routines when ESC is invoked, but
> I can't find anything that tells me why the code doesn't get triggered
> here ... ! This app is all about saving time and unnecessary
> mouse-clicks, so I wouldn't want to add any unnecessary delay/burden to
> the user ... just trying to keep the user out of trouble here ...
>
> Jeff
>

[Jeff]

Hi Jim,

This looked promising, Jim, but when I tested it out, got the same
results. Beginning to think I am just going to have to live with a
strong warning against escaping ...

Thanks for all your help!

Jeff

[Jeff]

Well, I did find a solution to this specific dilemma ... for anyone
interested, please see


http://www.experts-exchange.com/Appl..._21326799.html

(or if I am violating the Group's protocols by not posting the solution
here, intact, then please let me know ...)

Thanks again, Jim.

Jeff