SENDKEY method

[jason]

I'VE NEVER USED THE SENDKEYS METHOD BEFORE AND I'M HAVING A BIT OF A
PROBLEM.

All I want is for the computer to put the CapsLock on when a certain
workbook is activated, and then to turn off the CapsLock when the book
is deactivated; why doesn't the below work?

Private Sub Workbook_Activate()
Application.SendKeys "{CAPSLOCK}", True
End Sub

Private Sub Workbook_Deactivate()
Application.SendKeys "%{CAPSLOCK}", True
End Sub

Any help greatly appreciated

Jason

 

[Jim Rech]

Don't use Sendkeys if you can avoid it. So your first question should be
how to turn Caps lock on/off programmatically. See if this works:

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

Const KEYEVENTF_KEYUP = &H2
Const KEYEVENTF_EXTENDEDKEY = &H1

Sub NTKeyCapslockOn()
Dim lpbKeyState(0 To 255) As Byte
GetKeyboardState lpbKeyState(0)
If lpbKeyState(vbKeyCapital) = 0 Then
keybd_event vbKeyCapital, 0, KEYEVENTF_EXTENDEDKEY Or 0, 0 ''key
down
keybd_event vbKeyCapital, 0, KEYEVENTF_EXTENDEDKEY Or
KEYEVENTF_KEYUP, 0
End If
End Sub

Sub NTKeyCapslockOff()
Dim lpbKeyState(0 To 255) As Byte
GetKeyboardState lpbKeyState(0)
If lpbKeyState(vbKeyCapital) = 1 Then
keybd_event vbKeyCapital, 0, KEYEVENTF_EXTENDEDKEY Or 0, 0 ''key
down
keybd_event vbKeyCapital, 0, KEYEVENTF_EXTENDEDKEY Or
KEYEVENTF_KEYUP, 0
End If
End Sub


--
Jim Rech
Excel MVP
| I'VE NEVER USED THE SENDKEYS METHOD BEFORE AND I'M HAVING A BIT OF A
| PROBLEM.
|
| All I want is for the computer to put the CapsLock on when a certain
| workbook is activated, and then to turn off the CapsLock when the book
| is deactivated; why doesn't the below work?
|
| Private Sub Workbook_Activate()
| Application.SendKeys "{CAPSLOCK}", True
| End Sub
|
| Private Sub Workbook_Deactivate()
| Application.SendKeys "%{CAPSLOCK}", True
| End Sub
|
| Any help greatly appreciated
|
| Jason

 

[jason]

Hi Jim,

Thanks for the help.
What is the function GetKeyboardState. This doesn't seem to get
recognised. Do I need to add a reference so that it gets recognised or
is it another API function that I'm missing?

Could I just use the below? Suppose it won't know what state the users
capslock is already in!

Sub NTKeyCapslockToggle()
keybd_event vbKeyCapital, 0, KEYEVENTF_EXTENDEDKEY Or 0, 0
''key down
keybd_event vbKeyCapital, 0, KEYEVENTF_EXTENDEDKEY Or
KEYEVENTF_KEYUP, 0
End Sub

Help greatly appreciated
Jason

 

[Tom Ogilvy]

Here is the missing declaration. If you want to have it on for a certain
workbook, it doesn't seem like toggling would be the best approach. You
would turn it On when teh workbook is activated and turn it off when
deactivated:

Declare Sub GetKeyboardState Lib "user32" _
(pbKeyState As Byte)

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

Const KEYEVENTF_KEYUP = &H2
Const KEYEVENTF_EXTENDEDKEY = &H1

Sub NTKeyCapslockOn()
Dim lpbKeyState(0 To 255) As Byte
GetKeyboardState lpbKeyState(0)
If lpbKeyState(vbKeyCapital) = 0 Then
keybd_event vbKeyCapital, 0, _
KEYEVENTF_EXTENDEDKEY Or 0, 0 ''keydown
keybd_event vbKeyCapital, 0, _
KEYEVENTF_EXTENDEDKEY Or _
KEYEVENTF_KEYUP, 0
End If
End Sub

Sub NTKeyCapslockOff()
Dim lpbKeyState(0 To 255) As Byte
GetKeyboardState lpbKeyState(0)
If lpbKeyState(vbKeyCapital) = 1 Then
keybd_event vbKeyCapital, 0, _
KEYEVENTF_EXTENDEDKEY Or 0, 0 ''key down
keybd_event vbKeyCapital, 0, _
KEYEVENTF_EXTENDEDKEY Or _
KEYEVENTF_KEYUP, 0
End If
End Sub

 

[jason]

Cheers Tom: Now works fine
Looks pretty useful as I seem to be able to replace vbKeyCapital with
other constants, cven though I'm not too sure how these APIs work !

Jason