Escape key not working to stop workbook.open macro

[jryan15]

Hi all,

I am working on a workbook to do some updates automatically when the workbook opens, and I wanted to add a 20 second period to allow the user to abort the automation... However the enable cancel key method does not send the code to the error handler. Instead as the program is counting down, pressing escape seems to just bypass the one second application.wait and continues through the countdown.

Any idea how I can get the automation to actually stop when escape is pressed? Thanks!

Private Sub Workbook_Open()

'setup cancel key press
On Error GoTo xExit
Application.EnableCancelKey = xlErrorHandler


'notify user the application will wait for 10 second before running...  press escape to cancel...
tmp = 20
For i = 0 To tmp - 1
    Application.StatusBar = "Automation waking up...  " & tmp - i & " seconds to launch...  Press <escape> to abort the routine."
    Application.Wait (Now + TimeValue("0:00:01"))
Next

'call routines for automation
Application.StatusBar = "Beginning automation..."
AutoOpened = True
Update_All

'exit sub
Exit Sub


'go here for error or escape key
xExit:

If Err.Number = 18 Then
    MsgBox "Automatic update routine cancelled."""
End If

End Sub

 

[Jaafar Tribak]

Application.EnableCancelKey = xlErrorHandler is not always reliable. Try using the following alternative :

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If

Private Sub Workbook_Open()
    Const WAIT_PERIOD = 20
    Dim t As Single
    t = Timer
    Do While Timer - t <= WAIT_PERIOD
        If CBool(GetKeyState(VBA.vbKeyEscape) And &H10000) Then GoTo xExit
        Application.StatusBar = "Automation waking up...  " & WAIT_PERIOD - Int(Timer - t) & _
        " seconds to launch...  Press <escape> to abort the routine."
        DoEvents
    Loop
    Application.StatusBar = "Beginning automation..."
    AutoOpened = True
    Update_All
    Application.StatusBar = False
    Exit Sub
xExit:
    Application.StatusBar = False
    MsgBox "Automatic update routine cancelled."
End Sub

 

[jryan15]

This worked, thank you! The line for the private declare function is red for some reason, but I saved it any way and tested it. Works very well. Appreciate it!

 

[Jaafar Tribak]

This worked, thank you! The line for the private declare function is red for some reason, but I saved it any way and tested it. Works very well. Appreciate it!

Don't worry about the Declares that are in red. That's just conditional compiling based on the excel version. You can safely ignore it.

Also, please add Application.EnableCancelKey = xlDisabled at the start of the code ( after Dim t As Single )
This is to prevent the code from erroring out if the user keeps holding down the escape key repeatedly.

 

[jryan15]

Oh, thank you. Will do. Again really appreciate your help!