OnKey and macro settings

[tiredofit]

The following code was tested in Excel 2013, with the macro settings at option 4, ie Enable all macros (not recommended; potentially dangerous code can run).

Option Explicit

Private Sub Workbook_Open()
        
    Application.OnKey Key:="^n", Procedure:=vbNullString
            
End Sub

These are the steps I took. Please try to follow it step by step to see if you get the same problem.

Firstly ensure the macro settings in Excel is set to:

    Enable all macros (not recommended; potentially dangerous code can run).

I uploaded the workbook on the internet (say a Hotmail e-mail), then downloaded it onto my C drive.

Then I tried to open the file saved on my C drive via Windows Explorer.

When Excel opens, a message at the top shows:

PROTECTED VIEW Be careful - files from the internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View.

To the right of this message is a button - Enable Editing

Once I clicked on the button, I get a VBA error:

Run-time error '1004':

Method 'OnKey' of object '_Application' failed

When I click on Debug, this is the line it hangs on:

    Application.OnKey Key:="^n", Procedure:=vbNullString

If I now close the workbook WITHOUT saving, then open it via Windows Explorer, it runs without any problems.

Subsequent times also work.

Why is Excel 2013 exhibiting this weird behaviour?

Thanks

 

[RoryA]

It's an issue with protected mode. There are many application methods you get this error with and I have yet to find any way around the problem besides closing and reopening the file.

 

[tiredofit]

Thanks

 

[Jaafar Tribak]

It's an issue with protected mode. There are many application methods you get this error with and I have yet to find any way around the problem besides closing and reopening the file.

Rory,

Been playing around with this and thought maybe brievely delaying the execution of the code would work.
I used OnTime but oviously the OnTime Method itself generates an error just like OnKey does.

However, using a Windows Timer surprinsingly worked for me .. So in case you are interested , this is what I did :

Workbook Module :

Private Sub Workbook_Open()
    Call CallTimerProc
End Sub

Standard Module :

#If VBA7 Then
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Dim hTimer As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
    Dim hTimer As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Sub CallTimerProc()
    hTimer = SetTimer(0, 0, 0, AddressOf TimerProc)
End Sub

Sub TimerProc()

    KillTimer 0, hTimer

    Application.OnKey "^n", vbNullString
    
    MsgBox "Sucess!!"

End Sub

Subsequent calls to other Methods and Properties seem to work fine too all without having to close and reopen the file.