Num Lock gets off when opening the workbook

[boboivan]

Hi there!

I've just added the following code to my workbook

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
As Excel.Range)
If Target.Worksheet.Name = "2014" Then
    If Target.Column = 2 Or Target.Column = 5 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 19 Or Target.Column = 30 Or Target.Column = 34 Then
    Application.SendKeys ("%{down}")
    End If
End If
End Sub

and when I open it, the num lock turns off. Any idea why?

[ragulduy]

I added that to a blank workbook and tried and my numlock stays on - I'd suggest uploading an example workbook which demostrates the behaviour to check it isn't something else other than that code?

Edit: looking at google, it seems executing multiple sendkeys statements can turn numlock off (as a bug) - what is the macro supposed to do - maybe is there a way to do it without sendkeys?

[boboivan]

I added that to a blank workbook and tried and my numlock stays on - I'd suggest uploading an example workbook which demostrates the behaviour to check it isn't something else other than that code?

Edit: looking at google, it seems executing multiple sendkeys statements can turn numlock off (as a bug) - what is the macro supposed to do - maybe is there a way to do it without sendkeys?

The macro should automatically open the drop down lists in columns 2,5,10,11,19,30,34

[Solus Rankin]

I've seen send keys do this before.

Try commenting out

Application.SendKeys ("%{down}")

saving and then re-opening.

[boboivan]

I've seen send keys do this before.

Try commenting out

Application.SendKeys ("%{down}")

saving and then re-opening.

I didn't get it Solus !
What should I do here?

Edit:
I got it!
I've made a comment in the line, I've saved it, reopend it, and still turns off the Num Lock.

[ragulduy]

You could try turning numlock back on using API like this:

Option Explicit
Const VK_NUMLOCK = &H90
Private Type KeyboardBytes
kbByte(0 To 255) As Byte
End Type
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long
Private Declare Function GetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long
Private Declare Function SetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim kbArray As KeyboardBytes, CapsLock As Boolean, kbOld As KeyboardBytes
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
As Excel.Range)
If Target.Worksheet.Name = "2014" Then
    If Target.Column = 2 Or Target.Column = 5 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 19 Or Target.Column = 30 Or Target.Column = 34 Then
        If GetKeyState(VK_NUMLOCK) = 1 Then
            Application.SendKeys ("%{down}")
            TurnOn VK_NUMLOCK
        Else
            Application.SendKeys ("%{down}")
        End If
    End If
End If
End Sub
Private Sub TurnOn(vkKey As Long)
GetKeyboardState kbArray
kbArray.kbByte(vkKey) = 1
SetKeyboardState kbArray
End Sub

[Kenneth Hobson]

I am not a big fan of Sendkeys(). Even so, sometimes it will have to do.

I had thought of using API routine to check and set the numlock key status as well.

Here is an API method for a sendkeys sort of method. The advantage is that UAC status does not affect this method.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Worksheet.Name = "2014" Then
      If Target.Column = 2 Or Target.Column = 5 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 19 Or Target.Column = 30 Or Target.Column = 34 Then
        'Application.SendKeys "%{down}"
        KeyPlusKey &H12, &H28 'Vk_Menu+VK_Down (Alt+Down Arrow)
      End If
  End If
End Sub

Put this in a Module.

Option Explicit

'http://www.mrexcel.com/forum/showthread.php?p=2872719
'vk_keys, http://msdn.microsoft.com/en-us/library/ms927178.aspx

Declare Sub keybd_event Lib "user32.dll" _
  (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Sub Test_keybd_event1()
  'F5
  Key 116
  
  'Ctrl+T
  'KeyPlusKey 17, Asc("T")
  'or
  KeyPlusChar 17, "T"
End Sub

Sub Test_keybd_event2()
  'Send Ctrl+O
  KeyPlusChar 17, "O"    '11h or 17 - vk_control
  'Send Shift+Tab
  KeyPlusKey 16, 9   '10h or 16 = vk_Shift, 9 = vk_tab
  'Send Down and then Up to set focus to first item in FileOpenDlg()
  Key 40 '&H28 = 38, vk_down
  Key 38 '&H26 = 40, vk_up
End Sub

' Use this to send key command key plus a command key. e.g. Shift+Tab
Sub KeyPlusKey(str1 As Variant, str2 As Variant)
    KeyDown str1
    Key str2
    KeyUp str1
End Sub

' Use this to send key command plus a key combination. e.g. Ctrl+O
Sub KeyPlusChar(str1 As Variant, str2 As Variant)
    KeyDown str1
    Keys str2
    KeyUp str1
End Sub

' KeyDown() and KeyUp() for each character string in str.
Sub Keys(str As Variant)
    Dim i As Integer, s As String, j As Integer
    For i = 1 To Len(str)
        s = Mid(str, i, 1)
        For j = 1 To 330
          'Debug.Print j, Asc(s) - j
        Next j
        If Val(s) = 0 Then s = Asc(s)
        DoEvents
        Key Val(s)
    Next i
End Sub

' Release a key
Sub KeyUp(str As Variant)
  keybd_event str, &H9D, 2, 0
End Sub

' Press a key
Sub KeyDown(str As Variant)
  keybd_event str, &H9D, 0, 0
End Sub

' Press and release a key
Sub Key(str As Variant)
    KeyDown str
    KeyUp str
End Sub

For kicks, here two methods that I use for toggle keys like numlock.

Option Explicit

'http://www.vbaexpress.com/forum/showthread.php?38366
'http://www.mrexcel.com/forum/excel-questions/568216-open-send-inputs-another-program-excel.html

Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub test()
 'NUM_Off
 NUM_On
End Sub

Sub NUM_TOGGLE()
  'Toggle NUM-Lock key state
  keybd_event VK_NUMLOCK, 1, 0, 0
  keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub

Sub NUM_On()  'Turn NUM-Lock on
  If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
  End If
End Sub

Sub NUM_Off() 'Turn NUM-Lock off
  If (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
  End If
End Sub

Const NumLock_On = &H20
Const ScrollLock_On = &H40
Const CapsLock_On = &H80
Const vk_Scroll = &H91

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 GetKeyState Lib "user32" _
      (ByVal nVirtKey As Long) As Long

Sub KeyLock(myKey As String, State As Boolean)
  'State=True means to press key if state is off
  'myKey must be: Num, Scroll, or Caps as String type.
  
  Select Case True
    Case myKey Like "Num"
      If State <> CBool(GetKeyState(vbKeyNumlock)) Then PressKey (vbKeyNumlock)
    Case myKey Like "Scroll"
      If State <> CBool(GetKeyState(vk_Scroll)) Then PressKey (vk_Scroll)
    Case myKey Like "Caps"
      If State <> CBool(GetKeyState(vbKeyCapital)) Then PressKey (vbKeyCapital)
    Case Else
      'Nothing to do
  End Select
End Sub

Sub PressKey(theKey As Long)
  keybd_event theKey, 0, 0, 0 'press key
  keybd_event theKey, 0, &H2, 0 'release key
End Sub

Sub NumsOn()
  KeyLock "Num", True
End Sub

Sub NumsOff()
  KeyLock "Num", False
End Sub

Sub CapsOn()
  KeyLock "Caps", True
End Sub

Sub CapsOff()
  KeyLock "Caps", False
End Sub

Sub ScrollOn()
  KeyLock "Scroll", True
End Sub

Sub ScrollOff()
  KeyLock "Scroll", False
End Sub

[boboivan]

Thank you so much Kenneth!
Problem solved.