Ok as stated is this possible? I have an application I dont want anybody to
be able to stop the code. I am thinking an API call will be required, or is
there another way?
Could someone point me in the direction of how to write such an API Function?

Hi,

You can do this but I wouldn't until such time as your sure your code is
correctly debugged because you won't be able to break

Sub Sonic()
Application.EnableCancelKey = xlDisabled
For x = 1 To 10000
Cells(1, 1) = x
'A dummy loop to simulate your program
Next x
Application.EnableCancelKey = xlInterrupt
End Sub


ike

"Office_Novice" wrote:

Ok as stated is this possible? I have an application I dont want anybody to
be able to stop the code. I am thinking an API call will be required, or is
there another way?
Could someone point me in the direction of how to write such an API Function?

I had no idea. Thought i would be far more complex. Thanks.

"Mike H" wrote:

Hi,

You can do this but I wouldn't until such time as your sure your code is
correctly debugged because you won't be able to break

Sub Sonic()
Application.EnableCancelKey = xlDisabled
For x = 1 To 10000
Cells(1, 1) = x
'A dummy loop to simulate your program
Next x
Application.EnableCancelKey = xlInterrupt
End Sub


ike

"Office_Novice" wrote:

Ok as stated is this possible? I have an application I dont want anybody to
be able to stop the code. I am thinking an API call will be required, or is
there another way?
Could someone point me in the direction of how to write such an API Function?

Not always a good idea...

"My boss wanted some info from an Excel workbook and he had to
wait 15 minutes for ON's program to finish"

"Don't use that program from ON; you have to reboot to stop it"

"Why can't I control my own computer? We need new software."
--
Jim Cone
Portland, Oregon USA




"Office_Novice"

wrote in message
Ok as stated is this possible? I have an application I dont want anybody to
be able to stop the code. I am thinking an API call will be required, or is
there another way?
Could someone point me in the direction of how to write such an API Function?

There are three choices for handling the break key. The first,

Application.EnableCancelKey = xlInterrupt

is the standard mode and will cause the code to stop on the presently
executing line, entering Debug mode (unless the project is protected).

The second mode is

Application.EnableCancelKey = xlDisabled

This completely disables the break key. Use this with caution, though,
because if the code is improperly written and goes into a loop, you
cannot get out of it without using CTRL ALT DELETE or TaskMgr to stop
the process. You will lose all unsaved changes and possibly corrupt
the workbook.

The third mode is

Application.EnableCancelKey = xlErrorHandler

In this case, VBA will throw an exception when the break key is
pressed that you can handle with error handling code. For example,

On Error GoTo ErrH:
Application.EnableCancelKey = xlErrorHandler
' loop forever (illustration only)
Do Until False
DoEvents
Loop
Exit Sub ' no error. get out before error handler executes.
ErrH:
If Err.Number = 18 Then
MsgBox "Break key pressed"
Else
MsgBox "Some other error: " & vbNewLine & _
CStr(Err.Number) & " " & Err.Description
End If

In this code, the loop is just for illustration -- don't use that bit
of code. The EnableCancelKey is set to raise an exception, and when
that occurs, the On Error statement directs execution to the ErrH:
line label. That code tests the error number. An error 18 indicates
that the break key was pressed. In the code that handles error 18, you
can use Resume to exit from error mode and return the point of
execution to the line that threw the exception.

For information about error handling in general, see
http://www.cpearson.com/Excel/ErrorHandling.htm .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






On Sat, 18 Jul 2009 11:23:01 -0700, Office_Novice
wrote:

Ok as stated is this possible? I have an application I dont want anybody to
be able to stop the code. I am thinking an API call will be required, or is
there another way?
Could someone point me in the direction of how to write such an API Function?

Great advise Chip, Thanks. This appears to be procedure specific any idea how
somthing like option three would work fr the entire application?

"Chip Pearson" wrote:

There are three choices for handling the break key. The first,

Application.EnableCancelKey = xlInterrupt

is the standard mode and will cause the code to stop on the presently
executing line, entering Debug mode (unless the project is protected).

The second mode is

Application.EnableCancelKey = xlDisabled

This completely disables the break key. Use this with caution, though,
because if the code is improperly written and goes into a loop, you
cannot get out of it without using CTRL ALT DELETE or TaskMgr to stop
the process. You will lose all unsaved changes and possibly corrupt
the workbook.

The third mode is

Application.EnableCancelKey = xlErrorHandler

In this case, VBA will throw an exception when the break key is
pressed that you can handle with error handling code. For example,

On Error GoTo ErrH:
Application.EnableCancelKey = xlErrorHandler
' loop forever (illustration only)
Do Until False
DoEvents
Loop
Exit Sub ' no error. get out before error handler executes.
ErrH:
If Err.Number = 18 Then
MsgBox "Break key pressed"
Else
MsgBox "Some other error: " & vbNewLine & _
CStr(Err.Number) & " " & Err.Description
End If

In this code, the loop is just for illustration -- don't use that bit
of code. The EnableCancelKey is set to raise an exception, and when
that occurs, the On Error statement directs execution to the ErrH:
line label. That code tests the error number. An error 18 indicates
that the break key was pressed. In the code that handles error 18, you
can use Resume to exit from error mode and return the point of
execution to the line that threw the exception.

For information about error handling in general, see
http://www.cpearson.com/Excel/ErrorHandling.htm .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






On Sat, 18 Jul 2009 11:23:01 -0700, Office_Novice
wrote:

Ok as stated is this possible? I have an application I dont want anybody to
be able to stop the code. I am thinking an API call will be required, or is
there another way?
Could someone point me in the direction of how to write such an API Function?

somthing like option three would work fr the entire application?

It is somewhat "procedure specific" but in another way it is global.
The EnableCancelKey property persists for the entire Excel
application, even as code execution goes from one procedure to
another, and even when code execution terminates. So, you can set the
property once and it will remain at that value unless it is explicitly
changed elsewhere. However, most error handling is procedure
specific, and so you'd need to trap the error within each procedure or
at least in the initial procedure that calls others.

When an error occurs in any procedure, VBA looks in that procedure to
see how the error should be dealt with (GoTo, Resume, Resume Next,
etc). If no such directive exists in the procedure in which the error
was thrown, VBA searches up the call stack (the procedure that threw
the error, then the procedure that called that procedure, then the
procedure that called the caller and so on up the chain) until it
finds an On Error directive. If an error directive is found in an
upstream procedure, that error handler is invoked. If no On Error
directive is found, you get the standard VBA run time error dialog.

VBA's error handling is rather primitive, and it can be very tricky
and clunky to create a global error handler, because you would need to
test the error type and figure out where to resume or quit. Unlike
VBNET, which has a global Last Chance Exception Hander, errors in VBA
generally need to be dealt with within the procedure in which they
arise.

Of course, if you use Application.EnableCancelKey = xlDisabled, this
blocks the Break key in all procedures, not just the procedure in
which the value is set, and can be useful in that sense. However, be
SURE your code is correct, because you can't break out of bad code
when the break is disabled.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sun, 19 Jul 2009 08:10:00 -0700, Office_Novice
wrote:

Great advise Chip, Thanks. This appears to be procedure specific any idea how
somthing like option three would work fr the entire application?

"Chip Pearson" wrote:

There are three choices for handling the break key. The first,

Application.EnableCancelKey = xlInterrupt

is the standard mode and will cause the code to stop on the presently
executing line, entering Debug mode (unless the project is protected).

The second mode is

Application.EnableCancelKey = xlDisabled

This completely disables the break key. Use this with caution, though,
because if the code is improperly written and goes into a loop, you
cannot get out of it without using CTRL ALT DELETE or TaskMgr to stop
the process. You will lose all unsaved changes and possibly corrupt
the workbook.

The third mode is

Application.EnableCancelKey = xlErrorHandler

In this case, VBA will throw an exception when the break key is
pressed that you can handle with error handling code. For example,

On Error GoTo ErrH:
Application.EnableCancelKey = xlErrorHandler
' loop forever (illustration only)
Do Until False
DoEvents
Loop
Exit Sub ' no error. get out before error handler executes.
ErrH:
If Err.Number = 18 Then
MsgBox "Break key pressed"
Else
MsgBox "Some other error: " & vbNewLine & _
CStr(Err.Number) & " " & Err.Description
End If

In this code, the loop is just for illustration -- don't use that bit
of code. The EnableCancelKey is set to raise an exception, and when
that occurs, the On Error statement directs execution to the ErrH:
line label. That code tests the error number. An error 18 indicates
that the break key was pressed. In the code that handles error 18, you
can use Resume to exit from error mode and return the point of
execution to the line that threw the exception.

For information about error handling in general, see
http://www.cpearson.com/Excel/ErrorHandling.htm .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






On Sat, 18 Jul 2009 11:23:01 -0700, Office_Novice
wrote:

Ok as stated is this possible? I have an application I dont want anybody to
be able to stop the code. I am thinking an API call will be required, or is
there another way?
Could someone point me in the direction of how to write such an API Function?