4

What code does: I have a code that moves the mouse around the screen, takes printscreens and pastes it to excel.

Problem: For some reason, my code always (with absolutely no exceptions) turns the NUMLOCK key off after every run.

What I tried so far: I searched around and found the SendKeys (NUMLOCK), which in theory works (although it seems to be very problematic for users).

What I want to do: I want to turn the NUMLOCK on after each macro run,

Obs1: I have no idea what is causing the macro to turn it off in the first place. Fixing whatever is causing this would be ideal, but since I have no idea what the problem is, I first want to get my code functional. I am going to work on that as soon as find a way to turn the NUMLOCK key on.

Question: Can I do this using the SendKeys? Am I using it properly? Is there a better way?

Obs2: Since it is a much bigger code, as soon as this is solved, I am going to post another question with the entire code, and go over on what is causing the problem.

Code I am trying to sue to turn numlock on: 

Application.Sendkeys (NUMLOCK)

Also tried:

Application.Sendkeys ("NUMLOCK")

and

Application.Sendkeys {NUMLOCK}
R3uK
  • 14,417
  • 7
  • 43
  • 77
DGMS89
  • 1,507
  • 6
  • 29
  • 60
  • 2
    You may want to have a read here: http://stackoverflow.com/questions/25977933/sendkeys-is-messing-with-my-numlock-key-via-vba-code-in-access-form. It also may be a known issue: https://support.microsoft.com/en-us/help/179987/bug-multiple-sendkeys-statement-turns-off-numlock-key – sous2817 Feb 24 '17 at 14:12
  • @sous2817 Thanks for the comment. Already saw that question and tried it, still get the same problem. – DGMS89 Feb 24 '17 at 14:15
  • 2
    also, for what it's worth...isn't the syntax: SendKeys "{NUMLOCK}", True – sous2817 Feb 24 '17 at 14:24
  • @sous2817 Yes, I also tried including that, and where I have sendkeys in my code, I tried with both "true" and "false", all to no avail. – DGMS89 Feb 24 '17 at 14:27
  • 1
    The odd thing is that I've never seen this on my Dell machines, but it always seems to happen on HP laptops. – jkpieterse Feb 24 '17 at 14:30
  • Change it too `Application.Sendkeys "{NUMLOCK}", True : DoEvents` , close *everything* (VBE, Excel, and the other Office app that you use with this code), reopen and try! ;) – R3uK Feb 24 '17 at 14:30
  • 3
    And BTW, please don't use [tag:macros] tag which is specifically NOT for VBA ;) – R3uK Feb 24 '17 at 14:32
  • @R3uK Thanks for the commend. Just tried your suggestion, result is the same. I am starting to think I should make another sub just for turning the numlock on. And btw, you are right, that was an automatic SO suggestion of tag, I didn't even notice it until you said it. – DGMS89 Feb 24 '17 at 14:35
  • @jkpieterse - FWIW, I have a Dell, and it turns NumLock on for me when I use SenKeys. That's odd that you've never seen it! Hmm... I just chalk it up to `SendKeys` being weird, and I don't mind too much, since it's usually recommended to avoid using it. It's just another reminder to myself to keep its use to a minimum! – BruceWayne Feb 24 '17 at 14:47

5 Answers5

10

You can set the keystate directly with a couple of Windows API calls. Ported from the MSDN page for keybd_event function:

#If VBA7 Then
    Private Declare PtrSafe Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal bScan As Byte, _
                                                              ByVal dwFlags As LongPtr, ByVal dwExtraInfo As LongPtr)
    Private Declare PtrSafe Function GetKeyboardState Lib "user32.dll" (ByVal lpKeyState As LongPtr) As Boolean
#Else
    Private Declare Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal bScan As Byte, _
                                                      ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
    Private Declare Function GetKeyboardState Lib "user32.dll" (ByVal lpKeyState As Long) As Boolean
#End If  

Private Const KEYEVENTF_EXTENDEDKEY As Long = &H1
Private Const KEYEVENTF_KEYUP As Long = &H2
Private Const VK_NUMLOCK As Byte = &H90
Private Const NumLockScanCode As Byte = &H45

Private Sub ToggleNumlock(enabled As Boolean)
    Dim keystate(255) As Byte
    'Test current keyboard state.
    GetKeyboardState (VarPtr(keystate(0)))
    If (Not keystate(VK_NUMLOCK) And enabled) Or (keystate(VK_NUMLOCK) And Not enabled) Then
        'Send a keydown
        keybd_event VK_NUMLOCK, NumLockScanCode, KEYEVENTF_EXTENDEDKEY, 0&
        'Send a keyup
        keybd_event VK_NUMLOCK, NumLockScanCode, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0&
    End If
End Sub

Call it like this:

Sub Example()
    'Turn Numlock off.
    ToggleNumlock False
    'Turn Numlock on.
    ToggleNumlock True
End Sub
Comintern
  • 21,855
  • 5
  • 33
  • 80
  • I was about to suggest `keybd_event` too. Btw, you are missing a `Not` before the last `enabled` in the test after `GetKeyboardState`, the current one is the same as `If enabled Then`. ;) – R3uK Feb 24 '17 at 14:47
  • @Comintern Thanks for the answer. Can this type of api call can be used to set any type of keystate? – DGMS89 Feb 24 '17 at 14:52
  • @Comintern For this piece of code you posted, don't the variables have to be declared? – DGMS89 Feb 24 '17 at 14:55
  • @DGMS89 They are. The only 2 variables are `enabled`, which is declared as a parameter, and `keystate`, which is explicitly `Dim`'d. The rest are declared as constants. – Comintern Feb 24 '17 at 14:57
  • @Comintern Ok, many thanks. Just one thing, is there a reason for the part inside the else statement to be yielding an error? – DGMS89 Feb 24 '17 at 14:58
  • @Comintern I am probably doing this wrong, but I get both a compiling error for the entire first part (before the public sub), and the part inside the else is all red (but the code does not get there because of the previous error) – DGMS89 Feb 24 '17 at 15:01
  • 1
    @DGMS89 - Put everything above `Private Sub ToggleNumlock` at the very top of the module, right under `Option Explicit`. – Comintern Feb 24 '17 at 15:02
2

First of all, Copy and paste the following code in your Excel Sheet’s Module (Ex:-Module-1)...

Private Declare 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

Then, Copy and Paste the following in your Sheet's Code (Ex:- Sheet1 (Code))...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Range("XFD1").FormulaR1C1 = "=NumLock()"
      If Range("XFD1").Value = "FALSE" Then
      SendKeys "{NUMLOCK}"
      Else
      End If
      End Sub

Now Chill!!! For Each SelectionChange you make, Excel Refreshes itself and It makes sure that Numlock is On Always. Replace "Capslock" instead of Numlock if you need it so as the case may be.

Thanks. Sashi Elit :)

Sashi Elit
  • 21
  • 2
1

I found this solution so far the best and does not interfere with NUMLOCK. Put below code in a module and call it from anywhere in your project. The script object overwrites the SendKeys in VBA.

Public Sub Sendkeys(text as variant, Optional wait As Boolean = False)
   Dim WshShell As Object
   Set WshShell = CreateObject("wscript.shell")
   WshShell.Sendkeys cstr(text), wait
   Set WshShell = Nothing
End Sub

I found it in below thread:

SendKeys() permission denied error in Visual Basic

user10438136
  • 11
  • 1
0

I tried all the suggestions until I noticed that it's not (NUMLOCK) but {NUMLOCK}. This worked for me.

Sub Numlock() SendKeys "{NUMLOCK}" End Sub

Elizabeth Bennett
  • 1
-1

You almost had it! The correct coding is: Application.Sendkeys ("{NUMLOCK}")

Gunnar
  • 1