Locking out shift key stopping of autoexec macro

[trainman319]

MS gave the knowledgeable a key to stop the autoexec code in both Excel and Access.

Does anyone know a way to lock out this feature until the autoexec has finished processing?

 

[fumei]

Look up the Interactive property of the Application object. Setting to False at the start of your autoexec may help.





Gerry

 

[trainman319]

Hi Garry,

I turned application updating off on the very first line of my autoexec macro and turn it back on at the end of the autoexec process. But holding a shift key will allow the user to Totally skip the autoexec macro.

I think that I need to find the code(s) to disable the shift keys as part of the application, and enable the shift keys when it closes down.

I am open to any and all suggestions.

Neil

 

[Loomah]

Hi
A Google search turned this up. Note: it's for Access. I haven't tried it but the second link appears to be for a DB forum where someone has had problems.

Good Luck!

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q207793

http://dbforums.com/arch/110/2002/4/343813

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[MrTBC]

As far as I remember you need to write code to set the 'AllowBypassKey' property of the Application to False.
I hope this helps.

 

[trainman319]

Thanks Folks! I will check it out and let you know the result.

Neil

 

[trainman319]

Ok folks, we have hit a problem between MS Access and MS Excel.

Using Excel 2002, I have not been able to find a property 'AllowByPassKey'. I have looked at the Wookbook, the sheet and of course the Application. If I missed something, please clue me in.

Perhaps there is an API or Dll that would allow me to use this property.

 

[xlbo]

I believe the equivalent (or nearest) in excel is the EnableCancelKey property:

EnableCancelKey Property
See Also Applies To Example Specifics
Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD) user interruptions to the running procedure. Read/write XlEnableCancelKey.

XlEnableCancelKey can be one of these XlEnableCancelKey constants.
xlDisabled. Cancel key trapping is completely disabled.
xlErrorHandler. The interrupt is sent to the running procedure as an error, trappable by an error handler set up with an On Error GoTo statement. The trappable error code is 18.
xlInterrupt. The current procedure is interrupted, and the user can debug or end the procedure.

expression.EnableCancelKey

expression Required. An expression that returns one of the objects in the Applies To list.

Remarks
Use this property very carefully. If you use xlDisabled, there's no way to interrupt a runaway loop or other non – self-terminating code. Likewise, if you use xlErrorHandler but your error handler always returns using the Resume statement, there's no way to stop runaway code.

The EnableCancelKey property is always reset to xlInterrupt whenever Microsoft Excel returns to the idle state and there's no code running. To trap or disable cancellation in your procedure, you must explicitly change the EnableCancelKey property every time the procedure is called.

Example
This example shows how you can use the EnableCancelKey property to set up a custom cancellation handler.

On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler
MsgBox "This may take a long time: press ESC to cancel"
For x = 1 To 1000000 ' Do something 1,000,000 times (long!)
' do something here
Next x

handleCancel:
If Err = 18 Then
MsgBox "You cancelled"
End If

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MrTBC]

Sorry I should of read the post more closely. My suggestion was for Access.

 

[trainman319]

Hi Geoff,

This makes alot of sense, but I am not sure how to implement it. I have an Autpen procedure in a module which is the procedure that gets cancelled when opening the application with the shift key.

I know that each sheet can have an Auto_Activate but I am not sure when it is fired.

The workbook has an Open event which is where I put the line: application.EnableCancelKey = xlDisabled just to give it a try. Fear not it was on a copy just for a test. After saving and closing Excel, I reopened the application holding the shift key and it still bypassed the auto_open procedure.

Where am I going wrong or what do I need to do to make this work?

Neil

 

[xlbo]

As I said, it's the nearest to it, not the same thing - this one stops the bypassing of a macro with Ctrl+Break or Esc. I'm not sure there is a way to stop them using the shift key per se. The standard worksround is to get them to open a dummy workbook which has code that opens another workbook (the real one) and closes the dummy one. That way, if they open the dummy workbook and press shift, it will never open the real workbook

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[trainman319]

Hi Geoff,

I guess that I will have to use the standard work around. Thanks for the suggestions.

Neil