OnKey Error

**steveck** · 06-02-2023, 02:46 PM

I have an Excel 365 spread sheet where the user is not allowed to save with incomplete data. This works fine. But I need a way to override that for my own use and save anyway. When I use the defined key combination CTRL-ALT-PGDN I get an error 'argument not optional'. What is wrong with this code?

Application.OnKey "%^{PgDn}", "Secretsave"

Public Sub Secretsave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "In secret save"
ThisWorkbook.Save SaveChanges = True
End Sub

I want it to save the workbook. Thanks. Any help is appreciated.

**rollis13** · 06-02-2023, 06:08 PM

I understand that you will not need to SaveAs so use this code instead:

Private Sub SecretSave()
    MsgBox "In secret save"
    ThisWorkbook.Save
End Sub

**steveck** · 06-05-2023, 04:09 PM

I wan to save and close both. But my key combo doesn't even get me to the sub, i.e., I never see the "In SecretSave" message. Should the Application.OnKey "%^{PgDn}", "Secretsave" be in a Workbook_Open subroutine?

**rollis13** · 06-05-2023, 05:03 PM

Oh, I thought you were already clear about this part.
The activation of the OnKey sequence goes in the ThisWorkBook module:

Private Sub Workbook_Open()
    Application.OnKey "^%{PGDN}", "SecretSave"
End Sub

while the "SecretSave" macro goes in a standard module.

Perhaps it's better to add this other code to the ThisWorkBook module to disable the OnKey sequence when finished:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "^%{PGDN}", ""
End Sub

**steveck** · 06-05-2023, 05:19 PM

Whether I use Workbook_Open or BeforeClose, I get "Compile Error. Sub or Function not defined".

**rollis13** · 06-05-2023, 05:47 PM

No need to manually use those two macros, they are event macros and trigger automatically, the first on opening and the second on closing the file.
Sorry but can't replicate your issue. The attached file is a working file.