Close Excel if macro stopped

[Al]

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!

 

[VBA Dabbler]

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]

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!

 

[VBA Dabbler]

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]

"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!

 

[Myrna Larson]

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.

 

[Al]

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!

 

[Myrna Larson]

Yes, 2 more lines, but with {ESC} inside the quotes. Did you look at Help for
OnKey? It gives examples of all of this.

 

[Al]

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!