PDA

View Full Version : SendKeyys command turns off Numlock



morant05
07-24-2011, 05:05 AM
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
07-24-2011, 07:06 AM
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
11-12-2012, 03:50 PM
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
11-13-2012, 12:40 PM
What are you trying to do?

fishchoke
11-16-2012, 05:19 PM
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
11-18-2012, 12:00 PM
What is the other application?

fishchoke
11-26-2012, 02:02 PM
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.