Prevent users from breaking VBA code execution

[KerranJast]

Hi all,

This may have been answered before but I've trawled the 'net and
couldn't find a solution. I've a fairly big VBA project that pulls a
lot of data from a SQL DB and does some formatting. The macro code can
take up to 15minutes to complete depending on network traffic and that
means the user can hold [Escape] and break the execution of the code
leaving the workbook in a transitional state (I use a lot of protection
code to stop users modifying sections of the sheets and this can be
removed if the code is stopped in midsequence).

Is there anyway I can stop users from breaking the code execution? I
tried using Application.OnKey "{esc}", "" in my Auto_Open routine to
disable the [Escape] key when the workbook opens but this doesn't seem
to work. They can break the code and also stop pivottables and
querytables from completing their refreshing.

Any help would be much appreciated. Thanks.

 

[Jim Cone]

It's their computer not yours. On your machine do you want some
program to take over your computer and not let you use it for15 minutes.
Are you displaying a progress bar so the user knows it is working?
What do you do if the boss comes by and wants some data "now"?

That said, take a look at the "EnableCancelKey" property and read the
included warnings.
Consider using...Application.EnableCancelKey = xlErrorHandler
And in the error handler using...
If Err.Number = 18 then
MsgBox "User Interupt..." & vbCr & _
"Do you want to cancel this important program? ", _
vbYesNo + vbExclamation, "Important Program"

If the user cancels then run code to clean up and reconcile things.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"KerranJast"
wrote in message
Hi all,
This may have been answered before but I've trawled the 'net and
couldn't find a solution. I've a fairly big VBA project that pulls a
lot of data from a SQL DB and does some formatting. The macro code can
take up to 15minutes to complete depending on network traffic and that
means the user can hold [Escape] and break the execution of the code
leaving the workbook in a transitional state (I use a lot of protection
code to stop users modifying sections of the sheets and this can be
removed if the code is stopped in midsequence).

Is there anyway I can stop users from breaking the code execution? I
tried using Application.OnKey "{esc}", "" in my Auto_Open routine to
disable the [Escape] key when the workbook opens but this doesn't seem
to work. They can break the code and also stop pivottables and
querytables from completing their refreshing.
Any help would be much appreciated. Thanks.

 

[KerranJast]

Thanks for the reply Jim. That looks like it will help a lot.