Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
I have a protected worksheet with many protected hidden sheets and
cells. However, if the user is on an older version of Excel (i.e. 2000) and hits a button or esc while the macro is running, they can then hit the stop button, unhide the hidden sheets and move around and see the data, which I don't want to be accessible. If the user is on Excel 2002, which is what I used, this is not an issue - even if sheets are unhidden, they cannot see any cells because they are white on white and sheet is protected. This must be some bug w/ older versions of MS. Very weird. Anyhow, if someone runs the macro and stops it, I want Excel to close down so there is no chance of accessibility to the data. Is there a VB command that I can add in the macro that will accomplish this? Thanks, Al *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
Is your code running full-time while the file is open? If so, have you
thought of using event trapping? Have you thought of password protecting each worksheet? You might also check out the OnKey method. "Al" wrote: I have a protected worksheet with many protected hidden sheets and cells. However, if the user is on an older version of Excel (i.e. 2000) and hits a button or esc while the macro is running, they can then hit the stop button, unhide the hidden sheets and move around and see the data, which I don't want to be accessible. If the user is on Excel 2002, which is what I used, this is not an issue - even if sheets are unhidden, they cannot see any cells because they are white on white and sheet is protected. This must be some bug w/ older versions of MS. Very weird. Anyhow, if someone runs the macro and stops it, I want Excel to close down so there is no chance of accessibility to the data. Is there a VB command that I can add in the macro that will accomplish this? Thanks, Al *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
I have pw protected each sheet. No macro is not run full time. Basically, my worksheet has a macro button on it. When the macro button is clicked, the macro will gather data. When the macro initiates, it unprotects the entire workbook - it protects when the macro completes. In between, if the macro is interrupted, the hidden sheets can be unhidden. WHile those hidden sheets are protected, for some reason, in Excel 2000, the user can move around and see the cells in the fx box at the top of Excel (they are all white font on white background so nothing is SUPPOSED to be viewable). In Excel 2002, this is not an issue - the user can not move around the cursor in pw protected sheets. In Excel 2000, you can for some reason. Any ideas? What is event trapping and onkey? Thanks for your help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
Event trapping may not be the proper reference. Events are certain things
that happen during an Excel (or other applications for that matter) session, e.g., clicking on a worksheet, pressing a key on the keyboard, doublclicking something, and selecting a worksheet, chart, or cell - there are countless others. VBA provides objects, methods, and properties which can be used to test for and respond programmatically to a variety of 'events'. OnKey is a 'method' you can use to cause something to happen when the user presses a key. Search the online Help for such terms as 'event', 'error', 'err', 'OnKey', etc. and read up on them. You might also consider disabling the user's ability to 'break' code execution while your macro is running. I am not familiar with your security approach nor with the variations in behavior between different versions of Excel. If something is visible (not hidden), I'm not sure that you can prevent the user from navigating to it, unless you can find and employ a pertinent 'event' method or property. Hope this helps, VBA Dabbler "Al" wrote: I have pw protected each sheet. No macro is not run full time. Basically, my worksheet has a macro button on it. When the macro button is clicked, the macro will gather data. When the macro initiates, it unprotects the entire workbook - it protects when the macro completes. In between, if the macro is interrupted, the hidden sheets can be unhidden. WHile those hidden sheets are protected, for some reason, in Excel 2000, the user can move around and see the cells in the fx box at the top of Excel (they are all white font on white background so nothing is SUPPOSED to be viewable). In Excel 2002, this is not an issue - the user can not move around the cursor in pw protected sheets. In Excel 2000, you can for some reason. Any ideas? What is event trapping and onkey? Thanks for your help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
"You might also consider disabling the user's ability to 'break' code
execution while your macro is running." This would definitely be the optimal solution - what VB code do you use to accomplish this? Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
Put this line at the top of your code:
Application.OnKey "{BREAK}", "" and this one at the bottom: Application.OnKey "{BREAK}" See Help for OnKey for more information. On Mon, 21 Feb 2005 13:19:25 -0800, Al wrote: "You might also consider disabling the user's ability to 'break' code execution while your macro is running." This would definitely be the optimal solution - what VB code do you use to accomplish this? Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
Unfortunately, I was still able to break out the macro using the Esc
key. Do I need something add'l to disable Esc? Thanks. I followed this: Put this line at the top of your code: Application.OnKey "{BREAK}", "" and this one at the bottom: Application.OnKey "{BREAK}" *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
Yes, 2 more lines, but with {ESC} inside the quotes. Did you look at Help for
OnKey? It gives examples of all of this. On Mon, 21 Feb 2005 14:25:10 -0800, Al wrote: Unfortunately, I was still able to break out the macro using the Esc key. Do I need something add'l to disable Esc? Thanks. I followed this: Put this line at the top of your code: Application.OnKey "{BREAK}", "" and this one at the bottom: Application.OnKey "{BREAK}" *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close Excel if macro stopped
I did read up on the onkey and tried this:
Application.OnKey "{esc}", "" at the top. unfortunately, this didn't work and I could still interrupt the macro (also tried w/ capital ESC) w/ the ESC key. Not sure why. However, this one did the job. Application.EnableCancelKey = xlDisabled (I am not a developer/programmer by the way, so this might be very rudimentary). Thanks for your help - you put me on the right track! Al *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Close Excel with a Macro | Excel Discussion (Misc queries) | |||
Why have my Excel Macro Shortcuts stopped working? | Excel Worksheet Functions | |||
Macro to close excel | Excel Discussion (Misc queries) | |||
macro to close excel | Excel Discussion (Misc queries) | |||
why did the macro stopped working? | Excel Programming |