Onkey?

[Francis Hookam]

I'm nit sure how to use Onkey

I would like to be able to enter a single character into, or pass over, each
cell of a 9x9 box - the while/wend to set across and down is no prob but
will Onkey terminate at the end of the proceedure - it must or I'm in
trouble!

Can you show me how to use Onkey to enter each key action and terminate?

Thanks

Francis Hookham

 

[JE McGimpsey]

I'm nit sure how to use Onkey

I would like to be able to enter a single character into, or pass over, each
cell of a 9x9 box - the while/wend to set across and down is no prob but
will Onkey terminate at the end of the proceedure - it must or I'm in
trouble!

Can you show me how to use Onkey to enter each key action and terminate?

I'm really not sure what you're trying to do here...

OnKey allows you to intercept specific keys or combinations of keys.
It's a global setting - it doesn't just work for specific cells (though
you can use event macros to set and reset the OnKey intercept).

There's no need to terminate - the macro you associate with the key, or
key combination, will run and terminate before control is handed back to
XL.

So if I want to run a macro, "MyMacro", stored in a regular code module,
every time the user types the letter "a" in a particular workbook, then
I would put this in the ThisWorkbook module

Private Sub Workbook_Open()
Application.OnKey "a", "MyMacro"
End Sub

Private Sub Workbook_Activate()
Application.OnKey "a", "MyMacro"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "a"
End Sub

You could do the same thing with your 9x9 cell range. Put this in the
worksheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B2:J10")) Is Nothing Then
Application.OnKey "a", "MyMacro"
Else
Application.OnKey "a"
End If
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "a"
End Sub


Note that the system will intercept key combinations before XL sees
them, so you can't set an XL OnKey for CMD-m, for example (well, you can
set it, but it will never execute) because the system shortcut will
execute instead.

 

[Francis Hookam]

JE McG wrote:
"I'm really not sure what you're trying to do here..."

Maybe a better explanation is - I want to enter into each cell of a 9 x 9
box a single number, 1 to 9, and also to leave some cells blank

If a single keystroke can be be recognised, could a macro pause after moving
to the first cell, waiting for a keystroke - say a "2" - and on the
keystroke enter that number into that active cell, then move to the next
cell to pause for the next keystroke?

I would use a space bar keystroke to leave a blank cell

The macro for stepping from cell to cell across and down the 9 x 9 is no
problem - it is the code which waits for, and reacts to, the key stoke which
I have no idea how to write

Thank you once again

Francis Hookham

 

[Bob Greenblatt]

Maybe a better explanation is - I want to enter into each cell of a 9 x 9 box
a single number, 1 to 9, and also to leave some cells blank

If a single keystroke can be be recognised, could a macro pause after moving
to the first cell, waiting for a keystroke - say a "2" - and on the keystroke
enter that number into that active cell, then move to the next cell to pause
for the next keystroke?

I would use a space bar keystroke to leave a blank cell

The macro for stepping from cell to cell across and down the 9 x 9 is no
problem - it is the code which waits for, and reacts to, the key stoke which I
have no idea how to write

Thank you once again

Francis Hookham

Francis,

The problem is that a macro can NOT run while in Cell edit mode. I suppose
you might be able to construct a user form with the 9x9 entry fields and
control the entry in the form and then transfer the result to to the
worksheet cells.