SendKeys is messing with my NumLock key via VBA code in Access form

Question

I have the following code for an Access form. It appears as if the SendKeys is messing with my NumLock key by toggling it on and off as I open and close the form.

For perfectly valid reasons which I don't want to get into, I really do not want to completely hide the ribbon (I want the pull down menus still accessible) so the DoCmd.ShowToolbar command is not my preferred way of doing it.

Does anyone have any suggestions as to how I can modify the code below to accomplish what I want using the SendKeys command?

Using Access 2007 so the command

CommandBars.ExecuteMso "MinimizeRibbon"

is not available to me.

By the way, database will be distributed so solution must be contained within database.

Private Sub Form_Close()

' Unhide navigation pane
    DoCmd.NavigateTo "acNavigationCategoryObjectType"
    DoCmd.Maximize

' Maximize the ribbon
RibbonState = (CommandBars("Ribbon").Controls(1).Height < 75)

Select Case RibbonState
    Case True
        SendKeys "^{F1}", True
    Case False
        'Do nothing, already maximized
End Select
End Sub

Private Sub Form_Load()
' Hide navigation pane
    DoCmd.NavigateTo "acNavigationCategoryObjectType"
    DoCmd.Minimize
Debug.Print Application.CommandBars.Item("Ribbon").Height
' Minimize ribbon
RibbonState = (CommandBars("Ribbon").Controls(1).Height < 100)

Select Case RibbonState
    Case True
        'Do nothing, already minimized
    Case False
            SendKeys "^{F1}", False
End Select
End Sub

Take a look at http://support.microsoft.com/kb/179987/ (KB179987). This seems to be a known bug, but I can't test right now what exactly prevents it from happening. — Christian, Sep 23 '14 at 08:02
I tried adding DoEvents immediately after both SendKeys commands but it changed nothing. I was very confused as to how to use the DoEvents function. Can one of the VBA gurus help me modify this code to make it work? — user12059, Sep 23 '14 at 16:22

score 21 · Answer 1 · edited Aug 21 '17 at 14:09

21

It's a bug in Microsoft VBA. But there is a workaround.

Use F8 to run through the macro and find where it turns it off. It's usually after a SendKeys.

Then add an Sendkeys "{NUMLOCK}", True after the line to reverse the effect.

If you can't find it, just add it at the end and when it finishes, it will go back. Hopefully, if you add it during the show/hide process, it will work.

edited Aug 21 '17 at 14:09

M--

25,431
8
61
93

answered Apr 10 '15 at 01:35

icebird76

742
3
14
36

score 8 · Answer 2 · edited Aug 21 '17 at 14:07

8

This is caused by :

Sendkeys "any key", False

Instead of False as second parameter, use True.

edited Aug 21 '17 at 14:07

M--

25,431
8
61
93

answered Mar 03 '16 at 16:07

omari

97
1
2

3

Still occurs for me with `True` set. – EFH Jul 23 '20 at 20:43

score 8 · Answer 3 · answered Mar 02 '18 at 09:08

I had similar issue and I found solution on some vba forum. Instead of buggy Sendkeys you can simulate kyes like this.

    Option Explicit
'//WIN32API Declare
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

'//WIN32API Constant

Public Const KEYEVENTF_EXTENDEDKEY = &H1
Public Const KEYEVENTF_KEYUP = &H2
Public Const VK_CONTROL = &H11
Public Const VK_SHIFT = &H10
Public Const VK_F6 = &H75

Public Function PreviousTab()
    keybd_event VK_CONTROL, 0, 0, 0
    keybd_event VK_SHIFT, 0, 0, 0
    keybd_event VK_F6, 0, 0, 0
    keybd_event VK_F6, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_SHIFT, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_CONTROL, 0, KEYEVENTF_KEYUP, 0
End Function

Other keys can be found here vba forum This "previousTab" function just send Control+Shift+F6 key.

score 7 · Answer 4 · answered Nov 17 '17 at 21:52

When you do a final sendKeys command in your code, adding in {NUMLOCK} to the statement may do the trick, as noted by RodB and iceBird76. But this is not a good coding practice, and here is why: if anything is different from one time to the next when you run the macro, it may or may not work. I know this because I was experiencing a similar issue myself. When I would do a sendKeys command at the end of my program, sometimes the Num Lock would stay on, but other times it would stay off, just depending on certain variables in my spreadsheet (regardless of whether or not I included {NUMLOCK} in my last SendKeys statement).
I won't get into the details of my own variables, but the point is that to build a program/macro that will keep your Num Lock on consistently, you need to FIRST TEST TO SEE IF THE NUM LOCK IS ON OR OFF, then execute code based upon the present condition.

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

Public Function NumLock() As Boolean
    NumLock = KeyState(kNumlock)
    If (NumLock = True) Then
        MsgBox ("Num lock was off. Will turn back on now...")
        SendKeys "{NUMLOCK}", True
    Else: MsgBox ("Num Lock stayed on")
    End If
End Function

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


Sub myMainMethod()
    'do a function here that includes .SendKeys
    Call NumLock
End Sub

This sample program will give you a confirmation message as to whether the Num Lock is on or off, and turn it on if it is off.

score 7 · Answer 5 · answered Feb 06 '19 at 16:04

The SendKeys() function that is built-in VBA has really a side effect that causes NumLock to be deactivated. But you can use a workaround and call another implementation of the same function that is a part of WSCRIPT component (a part of Windows operating system). The following sample code shows, how a reference to this component can be made and then its method called:

Set WshShell = CreateObject("WScript.Shell")
WshShell.SendKeys "^g", True

This way, you get the same functionality (calling Ctrl-G keyboard shortcut in the example), but there is no issue with NumLock in this case.

score 5 · Answer 6 · edited Aug 21 '17 at 14:05

5

Right after your SendKeys statement add these 2 lines:

DoEvents
SendKeys "{NUMLOCK}{NUMLOCK}"

edited Aug 21 '17 at 14:05

M--

25,431
8
61
93

answered Aug 11 '17 at 04:19

Mendel Lowy

51
1
2

score 3 · Answer 7 · edited Aug 21 '17 at 14:08

3

This line caused my problem:

Application.SendKeys "%s"

SOLVED by changing to this:

Application.SendKeys "{NUMLOCK}%s"

There's no difference between adding {NUMLOCK} at the beginning or end of the string.

edited Aug 21 '17 at 14:08

M--

25,431
8
61
93

answered Jan 08 '16 at 05:07

RodB

31
1

score 1 · Answer 8 · answered Jul 19 '20 at 10:42

1

in my case application.senkeys method was creating this problem. so I used

with shell
.sendkeys "{}"
End with

Instead of

with shell
Application.sendkeys ("{}")
End with

answered Jul 19 '20 at 10:42

user1447820

21
5

score 1 · Answer 9 · answered Mar 24 '21 at 10:14

64bit VBA version

   Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
   Private Const VK_NUMLOCK = &H90

   If GetKeyState(VK_NUMLOCK) = 0 Then
      SendKeys "{NUMLOCK}", True
   End If

You could also check the return value from GetKeyState() before using SendKeys() and restore it by either executing SendKeys "{NUMLOCK}" or not when finished.

score 0 · Answer 10 · edited Aug 21 '17 at 14:06

0

SendKeys "^{HOME}", True was turning off the num lock so I just repeated the command and it turns it back on again:

SendKeys "^{HOME}", True
SendKeys "^{HOME}", True

edited Aug 21 '17 at 14:06

M--

25,431
8
61
93

answered Aug 30 '16 at 02:19

user6772440

1

user6788933 · Answer 11 · 2019-05-03T15:29:17.323

0

After trying many solutions. The most solid seems to be on the link below. Paste it to a Module.

http://access.mvps.org/access/api/api0046.htm

edited May 03 '19 at 15:29

answered May 03 '19 at 14:48

user6788933

285
2
10

Please ["add context around the link"](https://stackoverflow.com/help/how-to-answer). Thank you. – Sandra Rossi Oct 19 '20 at 06:11

k1dfr0std · Answer 12 · 2023-04-28T02:53:32.223

Posting an adaptation of SendETHToThisAddress's answer which worked for me, as their answer did not work (fully.). . .

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Const NumlockKey = 144

Public Function Reset_NumLock() As Boolean
    Reset_NumLock = KeyState(NumlockKey)
    If (Reset_NumLock = False) Then 'Checking for True gave incorrect results
      ''''''''''''''''''''''''''''''''''''''
      ' Modified This - removed MsgBox too '
      ''''''''''''''''''''''''''''''''''''''  
        With CreateObject("WScript.Shell")
          .SendKeys "{NUMLOCK}"
        End With
    Else
      Debug.Print "Num Lock was on!  WOOT!"
    End If
End Function

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

In my instance of Windows, the KeyState had to be False in order to accurately depict whether the Numlock key was off. Additionally, if I used Application.SendKeys, it NEVER saw the state change correctly and would always alternate the status of my numlock vs keep it on as what appears to be the goal of their function (meaning - if I turned it on, this would turn it off. If I turned it off, this would turn it on. . .).

Sending 2x {Numlock} also created a problem where it never saw the numlock turned ON, even though it was! (I believe this was due to it being in a specific state/condition when first attempt was made - still - I did not care for this.)

Additionally, their answer does not specify whether they are using Application.SendKeys or another adaptation of it, so I find it rather ambiguous, and prefer to unequivocally state what kind of adaptation I'm using. With the modified code below - I had 100% success rate with 200 tries (give or take, rapid fire, toggling the numlock key roughly every other interval). . .

That said - the only way to prevent the GetKeyState function to properly see my Numlock status was to NOT use Excel's SendKeys but instead use CreateObject("WScript.Shell") method.

Otherwise, the core of this answer is very close to what SendETHToThisAddress proposed, as at the core, I ultimately wanted a way to ensure that Numlock was always on.

SendKeys is messing with my NumLock key via VBA code in Access form

12 Answers12

Linked

Related