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



"Jim Rech" wrote in message ...
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
"jason" wrote in message
om...
| 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