OnKey

[Jaamie]

I have these OnKey initiation commands in an Autpen() routine:

Application.OnKey "~", "Check_Key" 'place Enter Key under control of
Application.OnKey "{ENTER}", "Check_Key"
Application.OnKey "{TAB}", "Check_Key"

I have these OnKey termination commands in an Auto_Close routine:

Application.OnKey "~", "" 'Enter Key back to normal
Application.OnKey "{ENTER}", "" 'Numeric Keypad Enter Key
Application.OnKey "{TAB}", ""

Problem: Once I close the spreadsheet containing the above, my enter and Tab keys stop functioning in other spreadsheets until I shutdown Excel and bring it back up.

Any ideas?
Thanks, Jim..

 

[Tom Urtis]

Place the code in your workbook module as Workbook_Activate and Workbook_Deactivate events.

 

[Jaamie]

Thank you for the quick reply.

How do I create Workbook_Activate and Deactivate Events?

BTW the OnKey Routines work fine in the spreadsheet that contains the Autpen and Close Subroutines.

What is the difference between Sub Autpen and Workbook_Activate, etc.

Thanks, Jim..

 

[Tom Urtis]

You would make things easier on yourself if you delete the code from the Autpen and Auto_Close procedures. The Workbook level Activation and Deactivation events would serve you better for manipulating OnKey settings (especially true given OnKey's sensitive personality). They would kick in when the workbook is opened or activated (that is, if you are in another workbook but click onto this one on your task bar, while in the same instance of Excel), and be disabled (brought back to normal) when the workbook is closed or you click onto another workbook in the same instance of Excel.

Place this in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following event procedures into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Workbook_Activate()
Application.OnKey "~", "Check_Key"
Application.OnKey "{ENTER}", "Check_Key"
Application.OnKey "{TAB}", "Check_Key"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "~"
Application.OnKey "{ENTER}"
Application.OnKey "{TAB}"
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Notice a difference between my code and yours, which is the omission of the trailing comma and null string quotes in your Auto_Close procedure (my Deactivate event), which are not needed.

 

[Jaamie]

Tom,

Thanks for the help. I'll try it.

Jim..