OnKey

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
224
Office Version
  1. 2016
Platform
  1. Windows
I have these OnKey initiation commands in an Auto_Open() 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..
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Place the code in your workbook module as Workbook_Activate and Workbook_Deactivate events.
 

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
224
Office Version
  1. 2016
Platform
  1. Windows
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 Auto_Open and Close Subroutines.

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

Thanks, Jim..
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
You would make things easier on yourself if you delete the code from the Auto_Open 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

Board Regular
Joined
Apr 16, 2003
Messages
224
Office Version
  1. 2016
Platform
  1. Windows
Tom,

Thanks for the help. I'll try it.

Jim..
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,527
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top