1

I know this has been put to the attention before, but I can't solve it. I have a button that calls a sub and in that sub I want to make sure that numlock is always on. The first time, i.e. if the numlock is off it turns it on. If it's already on, clicking the button once or twice keeps the numlock on, but clicking a third time turns the numlock off. Clicking again keeps it off. Clicking again turns it on again. So every 3 clicks it turns it off. I don't understand how to fix it. I ahve Excel 2019 bit and Windows 10 64 bit. Here's the code:

Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Const kCapital = 20
Private Const kNumlock = 144

Public Function CapsLock() As Boolean
    CapsLock = KeyState(kCapital)
End Function

Public Function NumLock() As Boolean
    NumLock = KeyState(kNumlock)
End Function

Private Function KeyState(lKey As Long) As Boolean
    KeyState = CBool(GetKeyState(lKey))
End Function


Public Sub ToggleNumlock(choice As Boolean)
Application.Volatile

If choice = True Then
    If NumLock = False Then SendKeys "{NUMLOCK}", True
Else
    If NumLock = True Then SendKeys "{NUMLOCK}", True

End If
End Sub

In the sub triggered by the button I have:

Application.SendKeys "{F2}"

and just after I have

      If NumLock = False Then
       ToggleNumlock (True)
      End If

Could it be the Sendkeys that causes trouble? Because I need it, is there a workaround? Thank you.

UPDATE TO MY CODE:

ActiveSheet.Range(CurrentCell).Value = "=" 
ActiveSheet.Range(CurrentCell).Select
Application.SendKeys "{F2}", True
Application.SendKeys "=", True
Application.SendKeys "{F2}"

I removed all the code regarding the numlock on off, etc. and trying this it works for now at least on my machine: I just push the keys twice. I'll check this on my office machine tomorrow.

UPDATED 2021-07-19 In my office (Windows 64 localized italian, Excel 2010) I have the same problem with numlock that toggles BUT also the comma on the numpad becomes a point (in Italy it's 3,14 not 3.14). I GIVE UP. Thanks to all who tried to help me. MS must really fix sendkeys.

Dolphin975
  • 157
  • 2
  • 10
  • There are a lot of hits when you search for "_vba sendkeys numlock turned off_". So `Sendkeys` seems to turn off `Numlock` even if you sent another keystroke – Storax Jul 18 '21 at 13:55
  • I do not see where you use `NumLockOn` in the updated code nor do I understand the purpose of your code. Would you mind explaining. – Storax Jul 18 '21 at 16:02

1 Answers1

3

Based on this article you can turn on Num Lock with the following code

Option Explicit
'https://www.vbarchiv.net/tipps/details.php?id=563

Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer


Private Declare Sub keybd_event Lib "user32" ( _
    ByVal bVk As Byte, _
    ByVal bScan As Byte, _
    ByVal dwFlags As Long, _
    ByVal dwExtraInfo As Long)
 
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2

Sub pressNumLock()
    ' press NUM-Lock drücken
    ' first key down and then key-up
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub

Sub NumLockOn()
    ' activate NUM-Lock (in case it is not activated)
    If Not (GetKeyState(vbKeyNumlock) = 1) Then
        pressNumLock
    End If
End Sub
Storax
  • 11,158
  • 3
  • 16
  • 33
  • I replaced my previous code with this one, but it keeps turning numlock on and off (every 2 clicks now). Really annoying. – Dolphin975 Jul 18 '21 at 14:23
  • 1
    I am afraid but you have to be more specific and post your adjusted code. The code I posted works for me with no problems. As said in my comment to your post `Sendkeys` will turn off 'Numlock` even if you send another keystroke. That just means you have to avoid using `Sendkeys`. – Storax Jul 18 '21 at 14:24
  • The code works perfectly. If you do not use `NumLockOn` Sub and use `pressNumLock` instead, it may create problems... :) Voted up. Just insert `PtrSafe` before `Sub keybd_event Lib...` . I will also suggest to change `NumLockOn` name in `SetNumLock`. – FaneDuru Jul 18 '21 at 15:24
  • Prefixing `PtrSafe` alone does'nt cover all cases (i.e. needed `LongPtr` types for pointers to a → handle or → memory location). - Afaik VBA7 PtrSafe API declaration for `keybd_event` would require **`dwExtraInfo`** as `LongPtr`, not as of `Long` type; even when passing it as `0` argument in function `pressNumLock()` a conversion would be necessary. – T.M. Jul 18 '21 at 17:50
  • 2
    According to [this article](https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-keybd_event) `keybd_event` is obsolete and you should use `SendInput` instead – chris neilsen Jul 18 '21 at 18:47