Application.EnableCancelKey = xlDisabled can be bypassed during Workbook.Open

[tv69]

Hi there,

I have a long string of macros that have to run when a workbook is opened. There are updates happening during this process that involves opening other workbooks.

In implementing the 'Application.EnableCancelKey = xlDisabled' when holding down the ESC key while this string of macros runs, the code can be interrupted anywhere there is a workbook being opened and also when a workbook is being saved (as updates are taking place along the way).

Two workbooks are attached the demonstrate this. The code in 'Book1.xlsm' is as follows:

Please Login or Register  to view this content.

With the code as is, pressing and holding down the ESC key after execution will bring it to a stop at the end of the first loop where the 2nd workbook is being opened. If the line of code that opens the 2nd workbook is commented out and the same process is followed, both loops will complete even though the ESC key is pressed and held down.

Is there a way to prevent this from happening?

Thank you for any input.

TV
Book1.xlsmBook2.xlsx

[ragulduy]

Do you get a "workbook couldn't be opened" error rather than a "code has been interrupted message"? You might be able to just build in an infinite error-checking loop to keep going until the workbook has been opened? For example:

Please Login or Register  to view this content.

[tv69]

Yes it seems to be a not able to open workbook error. It gives an error code of 1004 and says "Method 'Open' of objects 'Workbooks' failed.

Thank you for your input ragulduy. I added your suggested code but it still behaves exactly the same.

My worksheets are protected but when data needs to be written to them I am doing the standard 'unlock sheet, add new data, lock sheet' method.

This inability to disable the ESC key when workbooks are being opened or at a workbook save would cause three problems for me.

1. Worksheet intended to be protected is now unprotected and data that is normally locked can be edited

2. Supplemental workbooks are often being opened, data is checked and/or written to them, then saved and closed. These supplemental workbooks are intended for management. If the macros is halted via the ESC key when a supplemental workbook is opened, it is now visible to a user who is not supposed to see it.

3. With info moving between Excel workbooks via a macro, halting the macro via ESC would mean some data ends up where it is supposed to (portion of macro that has run) and other data will never make it where it is supposed to (macro events post ESC key) which would lead to data getting messed up.

I really hope there is solution to this.

TV

[tv69]

Does anyone have a solution to truly disable the ESC key so that it does not interrupt code during a Workbook.Open event or other such event?

Anyone???

TV