Consulting

Results 1 to 12 of 12

Thread: SendKeyys command turns off Numlock

  1. #1
    VBAX Newbie
    Joined
    Jul 2011
    Posts
    1
    Location

    SendKeyys command turns off Numlock

    Here's my situation...I have a spreadsheet where I want the Comment Box to open whenever data is typed into a cell. I found the code below and it works great with one small exception: the Numlock key turns off when the macro is enable in Excel. From everything I can find online, it seems to be the SendKeys command that is causing this to happen. Now, being honest, it's not a big deal to click the Numlock key to turn it back on while the macro is enabled. But I'd sure like to find out how this can be re-written so that the Numlock key doesn't turn off.

    Any help would be greatly appreciated.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Static sName As String
        Dim iLen    As Long
    
        If Len(sName) = 0 Then sName = Application.UserName & ":"
    
        With Target(1)
            If Intersect(.Cells, Range("B1:Q35")) Is Nothing Then Exit Sub
            If .HasFormula Then Exit Sub
    
            If .Value = Cells(.Row, "AB").Value Then
                If bHasComment(.Cells) Then .Comment.Delete
            
            Else
                .Select
    
                If Not bHasComment(.Cells) Then
                    .AddComment
                Else
                    iLen = Len(.Comment.Shape.TextFrame.Characters.Text)
                End If
    
                With .Comment.Shape.TextFrame
                    .AutoSize = False
                    .Characters(Start:=iLen + 1).Insert IIf(iLen, vbLf, "") & sName & vbLf
                    .Characters(Start:=iLen + 1, Length:=Len(sName)).Font.Bold = True
                End With
    
                With .Comment
                    .Visible = True
                    Application.SendKeys "+{F2}"
                    .Visible = False
                End With
    
            End If
        End With
    End Sub
    
    Function bHasComment(cell As Range) As Boolean
        On Error Resume Next
        bHasComment = cell.Comment.Parent.Address = cell.Address
    End Function

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    Welcome to the forum!

    I am not sure why you used SendKeys. When I did it manually, it only turned off NumLock once. Sendkeys should be avoided when possible. It won't work when UAC is on.

    In a Module:
    [VBA]Option Explicit

    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[/VBA]

  3. #3
    What do you recommend as the best alternative to sendkeys?

    I tried a few different things, currently I'm using this:

    Sub Sendkey(keys As Variant, Optional wait As Variant)
    WaitmsNOGC wtg
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    wsh.SendKeys keys, True
    Set wsh = Nothing
    DoEvents
    WaitmsNOGC wtg
    End Sub

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    What are you trying to do?

  5. #5
    I'm trying to use macros to activate another application (not excel), hit ctrl-a for 'select all', then hit ctrl-c for 'copy', then re-activate excel, and finally paste the copied information onto an excel spreadsheet.

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    What is the other application?

  7. #7
    The application is "Personal Communications iSeries Access for Windows". It's a text based client that interacts with an old IBM AS400 BPCS database. Basically I use excel macros and sendkeys to copy data from that database into excel, that way I can use all the functionality of excel on my data.

  8. #8
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    5
    Location
    Hi Kenneth, I know it's an old thread, I thought I'd mention that your routine works fine, but i found an anomaly. I managed to get into a situation where turning the numlocks on occurred twice in quick succession, resulting in the numlocks remaining off, emulated here:

    'Turn Numlock on
    If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
    End If

    'More code here in the full application,

    If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
    End If

    I guessed it was to do with timing, so the 2nd block toggled numlocks off again, you can actually see the LED flash on and off.

    Single stepping through worked perfectly, so as an experiment I modified the above to

    'Turn Numlock on
    If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
    End If

    'More code here in the full application,

    Call WaitABit (0.5) 'wait for 500mSec


    If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
    End If

    The delay fixed the problem, although the proper fix was to remove the extraneous block of code.

    Thanks for posting your solution, it's much more reliable than using Sendkeys.

  9. #9
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    Have you tried DoEvents after the first one? See last 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
    
    
    Sub NumLockOnTwiceTest()
      KeyLock "Num", True
      DoEvents
      KeyLock "Num", True
    End Sub
    Last edited by Kenneth Hobs; 06-24-2020 at 08:05 AM.

  10. #10
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    5
    Location
    Quote Originally Posted by Kenneth Hobs View Post
    Have you tried DoEvents after the first one? See last sub...
    I removed the first block of code because it was surplus to requirements. I've just tried putting DoEvents in the example above and it did the trick. I have DoEvents in the timer loop, so maybe it was that that fixed the problem rather than the delay per-se?

  11. #11
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    5
    Location
    After some experimentation I've found that the vb... constants work with your method. They're listed here if anyone wants them:

    https://docs.microsoft.com/en-us/off...code-constants

    Paradoxically this means I now don't need the numlocks-on routine, as I'm no longer using SendKeys!

  12. #12
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,937
    This is all you need:

    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Comment Is Nothing Then Target.AddComment
       Target.Comment.Text "snb"
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •