'OnKey and SendKeys starts a recursive loop, why?
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?
Solution 1:[1]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | tnavidi |
