CODEWITHSUNDEEP
vbams-accessms-access-2007sendkeys
user12059
user12059

Reputation: 743

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

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

Upvotes: 25

Views: 67714

Answers (15)

Marcucciboy2
Marcucciboy2

Reputation: 3257

I agree with BTjacker that it's likely best to just use keybd_event from the start instead of SendKeys. However, if you're not interested in that, I hope the code below helps - it works for me on Windows 10 (x64).

The answer above from SendETHToThisAddress is among many similar posts that did not work for me. After searching around, this post on MrExcel finally worked for me so I trimmed out some unnecessary operating system checks (as it's unlikely to find a user on Windows 95 or 98).

The original snippet is quite similar to this older SO answer and the example on this Microsoft help page. I'm also aware that my result is similar to this SO answer and Storax's but only the former worked for me.

Option Explicit

Private Const KEYEVENTF_EXTENDEDKEY = &H1
Private Const KEYEVENTF_KEYUP = &H2
Private Const VK_NUMLOCK = &H90
Private Const SCANCODE_NUMLOCK = &H45

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

Public Sub NumlockTOGGLE()

    If CheckButtonOnOff(VK_NUMLOCK) Then
        Debug.Print "Numlock is currently on.   Toggling OFF."
        NumlockOFF
    Else
        Debug.Print "Numlock is currently off.  Toggling ON."
        NumlockON
    End If
    
End Sub

Public Sub NumlockON()

    SetButtonOnOff VK_NUMLOCK, True             'force key state ON
    
End Sub

Public Sub NumlockOFF()

    SetButtonOnOff VK_NUMLOCK, False            'force key state OFF
    
End Sub

Private Function CheckButtonOnOff(ByVal virtualKey As Variant)
    
    CheckButtonOnOff = False
    
    Dim keyboardBuffer(255) As Byte
    GetKeyboardState keyboardBuffer(0)          'get state of all keys
    
    If keyboardBuffer(virtualKey) And 1 Then    'state of param key is ON
        CheckButtonOnOff = True
    End If
    
End Function

Private Sub SetButtonOnOff(ByVal virtualKey As Variant, ByVal buttonStateDesired As Boolean)
    
    Dim buttonState As Boolean
    buttonState = CheckButtonOnOff(virtualKey) 'get state of only param key
    
    If buttonState <> buttonStateDesired Then
        keybd_event virtualKey, SCANCODE_NUMLOCK, KEYEVENTF_EXTENDEDKEY Or 0, 0                 'key press
        keybd_event virtualKey, SCANCODE_NUMLOCK, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0   'key release
    End If
    
End Sub

Upvotes: 0

WilnexP
WilnexP

Reputation: 1

Use: Set SheetShell = CreateObject("WScript.Shell") SheetShell.SendKeys "~", True

'for sending the "Enter" key for example.

This approach works consistently.

Using the Application.SendKeys "~", True approach will toggle the the Numlock On and Off if you run the the script multiple times (for example)

Upvotes: 0

Quality Control
Quality Control

Reputation: 1

I randomly tried this command and my problem solved try it and let me know if it work my first command was this and always numlock turned off

SendKeys "{F12}", True

I replace it with this and problem solved SendKeys "{F12},{NUMLOCK}", True

Upvotes: 0

Jorge Urrutia
Jorge Urrutia

Reputation: 1

I know this is old, but I've been annoyed by this bug for years and it still happens a decade later. I just found an easy workaround. I don't really know how it works, but it does and I want others who find this thread to try it out, so here it goes:

Forget about checking NumLock status and the API and everything else. Just use your sendkeys command as you intended and add the following after the last sendkeys:

DoEvents
Sendkeys "^", True

I found that the PtrSafe GetKeyState function only returned the status correctly when the key was actually pressed, and not when the state was changed as a result of the VBA bug. DoEvents makes the bug go off, and then you send another key (I chose control because it had no effect on my application at all) and that triggers another instance of the bug, effectively undoing the first one.

During tests I noticed that CapsLock is also affected by this bug and it gets restored as well when forcing the bug once again.

I am aware that this is not a technical solution, but it's a very practical one that has consistently worked and effectively solved the problem for me. I'm sure others will find an explanation, and I will be very happy if at least one person finds this helpful.

Upvotes: 0

k1dfr0std
k1dfr0std

Reputation: 570

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.

Upvotes: 0

Darren Harvey
Darren Harvey

Reputation: 83

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.

Upvotes: 1

user1447820
user1447820

Reputation: 21

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

Upvotes: 1

Petr Michl
Petr Michl

Reputation: 71

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.

Upvotes: 7

BTjacker
BTjacker

Reputation: 121

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.

Upvotes: 9

SendETHToThisAddress
SendETHToThisAddress

Reputation: 3744

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.

Upvotes: 7

icebird76
icebird76

Reputation: 762

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.

Upvotes: 22

RodB
RodB

Reputation: 41

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.

Upvotes: 4

omari
omari

Reputation: 107

This is caused by :

Sendkeys "any key", False

Instead of False as second parameter, use True.

Upvotes: 9

user6772440
user6772440

Reputation: 1

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

Upvotes: 0

Mendel Lowy
Mendel Lowy

Reputation: 51

Right after your SendKeys statement add these 2 lines:

DoEvents
SendKeys "{NUMLOCK}{NUMLOCK}"

Upvotes: 5

Related Questions