This is what I found from 2004. But I can't figure out how to make it work. Either something has changed in Excel, or I am putting the code in the wrong places.
Tom Urtis says:
In the workbook module:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Workbook_Open()
Run "CapsOn"
Application.OnKey "{CAPSLOCK}", "CapsOn"
End Sub
Private Sub Workbook_Activate()
Run "CapsOn"
Application.OnKey "{CAPSLOCK}", "CapsOn"
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Run "CapsOn"
Application.OnKey "{CAPSLOCK}", "CapsOn"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "CapsOff"
Application.OnKey "{CAPSLOCK}"
End Sub
Private Sub Workbook_Deactivate()
Run "CapsOff"
Application.OnKey "{CAPSLOCK}"
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Run "CapsOff"
Application.OnKey "{CAPSLOCK}"
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
In a standard module:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long
Declare Function SetKeyboardState Lib "user32" (lppbKeyState As Byte) As Long
Sub CapsOff()
Dim Res As Long
Dim KBState(0 To 255) As Byte
Res = GetKeyboardState(KBState(0))
KBState(&H14) = 0
Res = SetKeyboardState(KBState(0))
End Sub
Sub CapsOn()
Dim Res As Long
Dim KBState(0 To 255) As Byte
Res = GetKeyboardState(KBState(0))
KBState(&H14) = 1
Res = SetKeyboardState(KBState(0))
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Worked for me when I tested it (Excel2003 WXP) but it's still early on a Sunday in California...see if it does what you need and post back if problems.
Tom Urtis
Microsoft Excel MVP 2008-2020