Shortcut to Insert Rows

[Juan Pablo González]

If you know (Or if you don't), you can use Control "+" to bring up the form to insert cells (Or automatically Insert Rows or Columns if you have an entire row or column selected).

Also, you can disable a shortcut key using the OnKey method, like this:

Application.OnKey "^c",""

to disable, and now, to re-enable:

Application.OnKey "^c"

I want to disable the Control "+" shortcut, I got it to work, strangely, with

Application.OnKey "^{+}",""

BUT, the problem is that this DOESN'T disable it for the "+" key in the Numeric Keyboard !! strange, isn't it ? any ideas ?

[Mark O'Brien]

Juan,

It is strange, but I seem to recall a few years ago coming across an article that told me that the mathematical operators on the number pad are mapped separately from the "main" keyboard. I wish I could find where I read that again.

One observation that I'll make though is that the /*+- all still work when Num Lock is off and that 12369874 act as the cursor keys etc.

Another thing I found in the help file, and I'm sure you did too is that the Sendkeys/Onkey function {ENTER} is actually the number pad ENTER and not the main keyboard ENTER, which is tilde (~).

If you find a solution please post it because this is going to bug me until it's solved. (I'll continue to look at it as well) I'm pretty sure we wont have to use any "Hook" API's.

[Mark O'Brien]

It appears that this is a well known limitation to the OnKey and SendKeys functions. I've been through the google archives and even looked up some stuff on key mapping.

[Colo]

Hi. Just information.
When I want to know keycode, use following.

On Userform:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)"
" MsgBox ""KeyCode is�F"" & KeyCode"
End Sub

The key code of + key the Numeric Keyboard is 107.

Sub Enable()
Application.OnKey "^{107}", "Test1"
End Sub

Sub Disable()
Application.OnKey "^{107}", ""
End Sub

Sub Test1()
MsgBox "it works"
End Sub

[Juan Pablo González]

Thanks a lot Colo

I was trying with something like that, but, was using another event. Thanks, I really appreciate it !

[Mark O'Brien]

Nice work Colo!!!

I spent about an hour trawling through the microsoft forum and no one could answer this.

I was reading about key mapping and apparently the "official" line from MS for not have this explicitly in VBA is because this keycode for the Keypad plus sign could change depending upon the local settings. I recall seeing 107, but didn't know you could use it in this way. I also recall a number in the 70's being used as well, but this may be a UNIX thing. (Besides, all three of us are using different settings I'm guessing, US, Japan and Colombia.)

Well done again Colo.

[Juan Pablo González]

Just one more thing. After experimenting a little with this, I found out what was pretty obvious, just wanted to make it clear for everyone.

With NumLock off, the "7" key in the Numeric keyboard, wich acts as Home, has a KeyCode of 36, but with NumLock on, it has a KeyCode of 103.

Another thing, NumLock's KeyCode is 144, but, I couldn't have it to react with the OnKey (I don't need to, but, I wanted to experiment with this possibility !)

Nothing else for now. Thanks again Colo. I was using the KeyPress wich uses a KeyAscii parameter.

[Colo]

Hi Juan and Mark. This time I just knew it.
Useally I am using Note type PC which has no numeric keyboard.
So, I don't know about keycode of "numeric keyboard" more than this.

But about "Application.OnKey", I have just one more information for everyone.
If need to use "ByVal or ByRef" we can use Chr(charcode) like following.
(Sorry I don't know what do I call it in English:I wish someone will help me.)

Pls Try.
Ctrl + UP or Down or Left or Right :Make Activecell.Interior.Color Red
Ctrl + Shift + UP or Down or Left or Right :Make Activecell.Interior.Color None

'----------------------------------------------------------------------

Option Explicit



Dim MyKey, cnt As Integer



Private Sub MyKeySet()

  MyKey = Array("^{UP}", "^{DOWN}", "^{LEFT}", "^{RIGHT}", _

                    "+^{UP}", "+^{DOWN}", "+^{LEFT}", "+^{RIGHT}")

End Sub



Sub Enable()

  Dim Val1, Val2, Val3

  Call MyKeySet



  Val1 = Array(-1, 1, 0, 0, -1, 1, 0, 0) '1st

  Val2 = Array(0, 0, -1, 1, 0, 0, -1, 1) '2nd

  Val3 = Array(True, True, True, True, False, False, False, False) '3rd



  For cnt = LBound(MyKey) To UBound(MyKey)

     Application.OnKey MyKey(cnt), "'MICI" & _

      Chr$(34) & Val1(cnt) & Chr$(34) & "," & _

      Chr$(34) & Val2(cnt) & Chr$(34) & "," & _

      Chr$(34) & Val3(cnt) & Chr$(34) & "'"

  Next

End Sub



Sub Disable()

    Call MyKeySet

    For cnt = LBound(MyKey) To UBound(MyKey)

        Application.OnKey MyKey(cnt)

    Next

End Sub



Sub MICI(RowOffset As Integer, ColumnOffset As Integer, flg As Boolean)

  Dim lngColorIndex As Long

  With ActiveCell

    Select Case flg

      Case True

        lngColorIndex = 3

      Case Else

        lngColorIndex = xlNone

    End Select

    .Interior.ColorIndex = lngColorIndex

    .Offset(RowOffset, ColumnOffset).Select

  End With

End Sub

'----------------------------------------------------------------------

_________________
With regards,
Colo



[ This Message was edited by: Colo on 2002-04-27 18:52 ]