PDA

View Full Version : [SOLVED] Application.Onkey



Opv
02-19-2016, 03:10 PM
Is there an issue with Application.Onkey in Excel 2007 under Windows 10? My onkey statement has all of a sudden stopped working.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim RecRng As Range
Set RecRng = Sheets("CHECKING").Range("J2:J" & LastDataRow)


If Sheets("CHECKING").AutoFilterMode = False _
Or Sheets("CHECKING").FilterMode = False Then GoTo CloseIt


If Not Intersect(Target, RecRng) Is Nothing Then
Application.OnKey "~", "reconcileIt"
End If


CloseIt:
Application.EnableEvents = True
Application.OnKey "~"
End Sub


I can change Application.Onkey "~" to Application.Onkey "{ENTER}" and it works with the numeric keypad. Actually, I just tried it and the tilde works with the numeric keypad. I wonder if my keyboard mapping has somehow gotten screwed up. Any ideas why the tilde would not work for ENTER on the keyboard, using Office 2007 on a Windows 10 laptop?

p45cal
02-20-2016, 02:55 AM
The last line of your sub has the line Application.OnKey "~" which disables that keypress shortcut; this line is executed no matter what.
Do you need an Exit Sub somewhere?

Opv
02-20-2016, 07:03 AM
The last line of your sub has the line Application.OnKey "~" which disables that keypress shortcut; this line is executed no matter what.
Do you need an Exit Sub somewhere?

Thanks. That is there by design to return normal functionality of the (keyboard) ENTER key after the "reconcileIt" code has run. The code as presented has worked flawlessly for several years until the past several months. I started having issues after upgrading to Windows 10. It's odd to me that "~", "{ENTER}" and "{RETURN}" all three currently work if I use the ENTER key on the numeric keypad but nothing seems to be triggered by pressing the ENTER key on the keyboard. I thought each of those options were mapped to specific keys. What could be causing all three to work from the numeric key pad and none of them to work for the regular ENTER key?

Paul_Hossler
02-20-2016, 07:23 AM
1. You're sure you're clicking somewhere in Sheets("CHECKING").Range("J2:J" & LastDataRow) ?

2. In Win10 Excel2016 at least a simple group of macros work



Option Explicit
Sub SetOnkey()
Application.OnKey "~", "UseOnkey"
End Sub

Sub UseOnkey()
MsgBox "Here"
End Sub

Sub UnsetOnkey()
Application.OnKey "~"
End Sub





Maybe see if this barebones group works by running SetOnkey, going to the WS and entering some data, and hitting the KB [Enter] key


If that works, then there's something else happening

Opv
02-20-2016, 07:53 AM
Thank you. Your test code properly triggers when I press the ENTER key on the keyboard and does not trigger with the ENTER key on the numeric key pad. So, as you say, something else has to be going on somewhere.

Opv
02-20-2016, 08:03 AM
I found the problem. Your test code prompted me to look more closely at my reconcileIt code. In some of my earlier testing I had ostensibly added an extra "Application.Onkey "~" in that block of code and it was canceling out my Worksheet_SelectionChange code. I can't tell you how many times I looked through that block of code trying to find something out of place and just overlooked it. All is well now. Thanks for helping me see the need to get my head out of the box.

p45cal
02-20-2016, 08:17 AM
Thanks. That is there by design to return normal functionality of the (keyboard) ENTER key after the "reconcileIt" code has run. The code as presented has worked flawlessly for several years until the past several months. I started having issues after upgrading to Windows 10. It's odd to me that "~", "{ENTER}" and "{RETURN}" all three currently work if I use the ENTER key on the numeric keypad but nothing seems to be triggered by pressing the ENTER key on the keyboard. I thought each of those options were mapped to specific keys. What could be causing all three to work from the numeric key pad and none of them to work for the regular ENTER key?
I'm pretty sure the ~ is the main keyboard Enter key. It is on my desktop.
Whenever you change one Onkey line in your macro you must change the other to match in order properly to test.
If the sheet called CHECKING is not the same sheet as the code is in then the line
If Not Intersect(Target, RecRng) Is Nothing Then
can NEVER be true and so the line:
Application.OnKey "~", "reconcileIt"
will NEVER be run.
However, I'm more than guessing that the CHECKING sheet IS the same as the sheet that the Selection_Change code is in, in which case, to avoid any problems with sheet name changes you don't need to refer to the CHECKING sheet in the code, this wll do:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RecRng As Range
Set RecRng = Range("J2:J" & LastDataRow)

If AutoFilterMode = False Or FilterMode = False Then GoTo CloseIt

If Not Intersect(Target, RecRng) Is Nothing Then
Application.OnKey "~", "reconcileIt"
End If


CloseIt:
Application.EnableEvents = True
Application.OnKey "~"
End Sub

Next, to illustrate what I was saying earlier, put a break point at the line
Application.OnKey "~", "reconcileIt"
by putting the text cursor on that line and pressing the F9 keyboard key.
This will cause code execution to stop at that line which will do 2 things:
1. confirm to you that conditions are such that line is about to be executed, and
2. will allow you to continue, one line at a time to step through the code to show you which lines are being executed.

So, set up the conditions that will make that line execute by filtering for something (just the presence of an autofilter is NOT enough - a filter has to be actively filtering something) and make sure you're selecting something in the RecRng range.
This should cause execution to stop at that line, with a yellow highlight.
Now press the F8 key, repeatedly, each time seeing where execution is getting to.
The first press will execute the Onkey line - hurrah!
Now press it again, and again. The third time you press it you'll get to the line:
Application.OnKey "~"
effectively disabling the shortcut key microseconds after it's been set, with no chance whatsoever of pressing the Enter key in that time.

A possible solution might be to have something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RecRng As Range
Set RecRng = Range("J2:J" & LastDataRow)

If AutoFilterMode = False Or FilterMode = False Then GoTo CloseIt

If Not Intersect(Target, RecRng) Is Nothing Then
Application.OnKey "~", "reconcileIt"
Application.EnableEvents = True
Exit Sub
End If


CloseIt:
Application.EnableEvents = True
Application.OnKey "~"
End Sub

Paul_Hossler
02-20-2016, 02:56 PM
I found the problem. Your test code prompted me to look more closely at my reconcileIt code. In some of my earlier testing I had ostensibly added an extra "Application.Onkey "~" in that block of code and it was canceling out my Worksheet_SelectionChange code. I can't tell you how many times I looked through that block of code trying to find something out of place and just overlooked it. All is well now. Thanks for helping me see the need to get my head out of the box.

Glad it pointed you in the right direction