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

J

Jeff

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:

Option Explicit

Private Declare Function LockWindowUpdate Lib "USER32" _
(ByVal hwndLock As Long) As Long
Private Declare Function GetDesktopWindow Lib "USER32" () As Long

Sub RemoveSubtotals()

'RemoveSubtotals Macro

Application.EnableCancelKey = xlErrorHandler

On Error GoTo handleCancel

If ActiveSheet.ProtectContents = False Then

Unprotected:

Application.StatusBar = "The more Subtotals there are in the
selected Region, the longer it takes to Remove Subtotals (large arrays
will take a while ...). Please wait ..."

Call WindowUpdating(False)

Selection.RemoveSubtotal

Application.StatusBar = False 'set StatusBar to "Ready"

Call WindowUpdating(True)

ElseIf ActiveSheet.ProtectContents = True Then

Call ProtectedSheetErrorHandler

'Test for protection again

If ActiveSheet.ProtectContents = False Then

'Worksheet is now unprotected so resume procedure above

Resume Unprotected

Else

Exit Sub

End If

End If

Exit Sub

handleCancel:

Call WindowUpdating(True)

Dim response As Integer

If Err.Number = 18 Or Err.Number = 1004 Then

response = MsgBox(prompt:="This message is appearing because
this function has been interrupted. Intentionally interrupting a" &
vbCrLf & "macro process may produce unexpected results. The specific
error that was triggered was:" & vbCrLf & vbCrLf & "Error Number: " &
Err.Number & " " & vbCrLf & "Error Description: " &
Err.Description & vbCrLf & vbCrLf & "To resume this process, click
'OK'. Otherwise, if you are sure you want to cancel, click Cancel to
end.", Buttons:=vbOKCancel)

Else

MsgBox "Error Number: " & Err.Number & vbCrLf & Err.Description

Exit Sub

End If

'If user clicks OK, then Resume; otherwise the process will end

If response <> vbCancel Then

Err = 0
Resume

End If

End Sub
'*************************************************************************************
Sub WindowUpdating(Enabled As Boolean)

'Courtesy of Dave Peterson email: (e-mail address removed)
'http://www.excelforum.com/printthread.php?s=&threadid=247463

' "Completely Locks the Whole Application Screen Area, including
dialogs and the mouse.
' You can turn off all of the windows screen updates -- but it this
code stops, you'll
' be rebooting your PC:"

Dim Res As Long

If Enabled Then

'Unlock screen area

LockWindowUpdate 0
Application.ScreenUpdating = True 'Not part of Dave's code
- I just added to be sure

Else

'Lock at desktop level

Res = LockWindowUpdate(GetDesktopWindow)
Application.ScreenUpdating = False 'Not part of Dave's code
- I just added to be sure

End If

End Sub
'*************************************************************************************
Public Sub ProtectedSheetErrorHandler()

Dim response, response2 As Integer

Call WindowUpdating(True)

response = MsgBox(prompt:="Worksheet is Protected - To perform this
function, you must Unprotect the Worksheet first." & Chr(13) & "Click
'OK' to Unprotect the Worksheet now or Cancel to end.",
Buttons:=vbOKCancel)

If response = vbCancel Then

'Worksheet is still protected, so advise

response2 = MsgBox(prompt:="Function NOT available because
Worksheet is Protected. Click OK to to continue.", Buttons:=vbOK)

Exit Sub

ElseIf response = vbOK Then

ActiveSheet.Unprotect

Exit Sub

End If

End Sub
 
J

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 said:
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:
Click to expand...
- snip -
 
J

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
 
J

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
 
J

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
 
J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook events stop work randomly 0
Trying to run excel vba code when cell value changes from empty to entered value. 1
Outlook Cannot access more than the Outlook VBA 250 Mail Items 1
Run-Time Error 2501 The OpenForm action was cancelled 1
Saving outlook attachment to a different file 0
Meaning of On Error Goto 0 5
How to get my macro as rule in Outlook? 0
proper code placement 4

Top