Nuno Nogueira February 2016

Prevent Excel VBA code interruption with Esc key

How can I prevent Excel VBA Code from being interrupted by the user by pressing the Esc Key?

I'm trying the following code on ThisWorkbook:

Private Sub Workbook_Open()
    On Error Resume Next
    Application.EnableCancelKey = xlDisabled
    Application.DataEntryMode = True

    Call WebPage
End Sub

But it's not working since if I press Esc when the workbook opens, the code breaks:

enter image description here

So it seems the code is interrupted even before the workbook opens. Is there a way to fix this?

Thanks for helping!

Answers


Grade 'Eh' Bacon February 2016

It seems that your workbook is big enough that there is a time delay between opening and running code, leaving an opportunity for the user to hit esc in the meantime. Perhaps instead of calling WebPage on the workbook opening, you could have the file be simply a 'landing page' with instructions and a single button to click. This gives the user control over executing the macro, but also gives you time to load your disabling of the escape key before the macro is run.

Also, if you have confidence that your error handling will return all functionality to normal you could use this instead:

Application.InterActive = False

This would prevent any key instructions being entered, including esc & command break.

Post Status

Asked in February 2016
Viewed 3,897 times
Voted 14
Answered 1 times

Search




Leave an answer