Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from breaking VBA code execution
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 ------------------------------------------------------------------------ KerranJast's Profile: http://www.excelforum.com/member.php...o&userid=34971 View this thread: http://www.excelforum.com/showthread...hreadid=547088 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from breaking VBA code execution
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from breaking VBA code execution
Thanks for the reply Jim. That looks like it will help a lot. -- KerranJast ------------------------------------------------------------------------ KerranJast's Profile: http://www.excelforum.com/member.php...o&userid=34971 View this thread: http://www.excelforum.com/showthread...hreadid=547088 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code to prevent users from adding new worksheets | Excel Worksheet Functions | |||
How to I prevent a row from breaking across pages? ... | Excel Discussion (Misc queries) | |||
How to I prevent a row from breaking across pages? | Excel Discussion (Misc queries) | |||
prevent users from overwriting other users data | Excel Worksheet Functions | |||
MACRO OR VBA CODE TO PREVENT USERS FROM PRINTING THE CONTENTS OF A WORKSHEET? | Excel Programming |