OnKey and SendKeys starts a recursive loop, why?-CodePudding

Every key pressed a-z A-Z 0-9 starts a macro called LetterPress with the key pressed as a parameter. Basically I have an Application.OnKey for every key a-z A-Z 0-9

If the active cell is a certain cell I want to start a sub, else I just want to send the key like normal. That starts a recursive loop that doesn't end. This was the original code:

Sub LetterPress(B as String)
If Application.ActiveCell.Row = 3 And Application.ActiveCell.Column = 5 And Application.ActiveSheet.Name = "Search" And Not IsNumeric(B) Then
    Call Search1(B)
Else
    Application.SendKeys B       'This line starts a recursive loop
End if

I tried to reset OnKey, send the key and then assign the key back to the macro. But the last row (the assign bit) still starts a recursive loop.

Sub LetterPress(B as String)
If Application.ActiveCell.Row = 3 And Application.ActiveCell.Column = 5 And Application.ActiveSheet.Name = "Search" And Not IsNumeric(B) Then
    Call Search1(B)
Else
    Application.OnKey B          'Reset Application.OnKey so a recursive loop doesn't start. This line works.
    Application.SendKeys B       'Send the string B (a letter). This works too.
    Application.OnKey B, "'LetterPress """ & B & """'"      'Assign whatever is in string variable B back to the 
End if                                                      'macro, with it self as the parameter. 
                                                            'This line somehow triggers B and starts the 
                                                            'recursive loop.

Anyone knows why?

CodePudding user response：

Your attempt is not that bad. The problem is that with Application.SendKeys B, you are sending the key, but it is not processed. First your code is executed until the very end, then Excel gets back the control and can process the key stroke. At that time, your OnKey-definition is already active.

There is, however, a simple solution: Give Excel the opportunity to do the work. You can do so with DoEvents.

Application.OnKey B
Application.SendKeys B       
DoEvents
Application.OnKey B, "'LetterPress """ & B & """'"

I did a test and at least it worked for me - I am curious if you can confirm.

CodePudding user response：

This may work:

Sub LetterPress(B as String)
If Application.ActiveCell.Row = 3 And Application.ActiveCell.Column = 5 And Application.ActiveSheet.Name = "Search" And Not IsNumeric(B) Then
    Call Search1(B)
Else
    Application.OnKey B          'Reset Application.OnKey so a recursive loop doesn't start. This line works.
    Application.SendKeys B       'Send the string B (a letter). This works too.
    Application.DoEvents
    Application.OnKey B, "'LetterPress """ & B & """'"      'Assign whatever is in string variable B back to the 
End if                                                      'macro, with it self as the parameter. 
                                                            'This line somehow triggers B and starts the 
                                                            'recursive loop.

That being said; I have never actually tried using DoEvents() function. Let me know if it works.