Contine after OnKey function called

[Tigs81]

Hi
I am trying to work out how to resume the normal key function, after I have done some calculations after a particular key has been pressed on a certain cell.

For example i know that i can use the onkey to reference a subroutine to do the calculations. But i dont know to get it to resume normal key function after the calculations have been done.

Application.OnKey "a", "mySub"
and i also not how to cancel the onkey subroutinue call.
Application.OnKey "a"

But I dont know how to get it to type the letter "a" in this example after the subroutine has been completed.

Thanks
Tigs

 

[AlphaFrog]

This turns off or cancels the OnKey Event for "a"

Application.OnKey "a", ""

You could put this at the end of the mySub macro.

 

[Tigs81]

AlphaFrog

Yes your right it does turnoff/cancel the onkey event but unfortunately it doesnt type the "a" into the cell.

which is what i am trying to achieve.
Is there some other way of capturing a key press and running a function?

Thanks
Tigs

 

[AlphaFrog]

AlphaFrog

... doesnt type the "a" into the cell.

which is what i am trying to achieve.

This will put "a" in the active cell...

ActiveCell.Value = "a"

 

[Tigs81]

Sorry for the delay.
Yes, that would work in that instance.

But ideally i would like to be able to press a key it runs some code and then I want the normal key behavior to execute.

eg on cells b1:b10, i would like it so that when the enter key is pressed the active cell moves across 2 columns.

So i have thought that using the intersect function to determine the activecell location and if its in the listed cells, and the enter button was pressed then the active cell moves across 2 columns. But if its not in the listed cell range then process the normal enter key behaviour normally.

I have tried incorporating the sendkeys function but that only results in a loop.

Thanks
Tigs

 

[AlphaFrog]

eg on cells b1:b10, i would like it so that when the enter key is pressed the active cell moves across 2 columns.

So i have thought that using the intersect function to determine the activecell location and if its in the listed cells, and the enter button was pressed then the active cell moves across 2 columns. But if its not in the listed cell range then process the normal enter key behaviour normally.

• Right-click on the sheet tab
• Select View Code from the pop-up menu
• Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1:B10"), Target) Is Nothing Then Target.Offset(, 2).Select
End Sub