Turning NUMLOCK on at the end of a macro run

Question

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}

Answer 1

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

Answer 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 :)