SendKeyys command turns off Numlock

[morant05]

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

[Kenneth Hobs]

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:
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

[fishchoke]

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

[Kenneth Hobs]

What are you trying to do?

[fishchoke]

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.

[Kenneth Hobs]

What is the other application?

[fishchoke]

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.

[RtbComp]

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.

[Kenneth Hobs]

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

[RtbComp]

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?

[RtbComp]

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/office/vba/language/reference/user-interface-help/keycode-constants

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

[snb]

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