SendKeys is messing with my NumLock key via VBA co

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

Answer 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

Answer 2

This is caused by :

Sendkeys "any key", False

Instead of False as second parameter, use True.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

Right after your SendKeys statement add these 2 lines:

DoEvents
SendKeys "{NUMLOCK}{NUMLOCK}"