I've saved this from other posts--it includes other stuff, but also the
capslock.
Option Explicit
' Code from "VBA Developer's Handbook" (Sybex, 1997):
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) _
As Integer
Private Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) _
As Long
Private Declare Function SetKeyboardState Lib "user32" (lppbKeyState As Byte) _
As Long
Function GetCapslock() As Boolean
' Return or set the Capslock toggle
GetCapslock = CBool(GetKeyState(vbKeyCapital) And 1)
End Function
Function GetNumlock() As Boolean
' Return or set the Numlock toggle.
GetNumlock = CBool(GetKeyState(vbKeyNumlock) And 1)
End Function
Sub SetCapslock(Value As Boolean)
' Return or set the Capslock toggle.
Call SetKeyState(vbKeyCapital, Value)
End Sub
Sub SetNumlock(Value As Boolean)
' Return or set the Numlock toggle.
Call SetKeyState(vbKeyNumlock, Value)
End Sub
Private Sub SetKeyState(intKey As Integer, fTurnOn As Boolean)
Dim abytBuffer(0 To 255) As Byte
GetKeyboardState abytBuffer(0)
abytBuffer(intKey) = CByte(Abs(fTurnOn))
SetKeyboardState abytBuffer(0)
End Sub
Sub Caps_on()
If GetCapslock = False Then Call SetKeyState(vbKeyCapital, True)
End Sub
Sub Caps_Off()
If GetCapslock = True Then Call SetKeyState(vbKeyCapital, False)
End Sub
Sub auto_open()
Application.OnKey "{CAPSLOCK}", "TurnItOff"
End Sub
Sub auto_close()
Application.OnKey "{CAPSLOCK}"
End Sub
Sub TurnItOff()
If GetCapslock = True Then
Call SetKeyState(vbKeyCapital, False)
Beep
End If
End Sub
If you create a workbook with this code in it, then the auto_open procedure will
run and turn off the capslock whenever you click it.
If the OP is new to macros, then the OP can read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
ps. There's another option you may like better (I would).
Go to windows control panel|Accessibility options|keyboard tab
Turn on the ToggleKeys option.
You'll hear a beep when you hit capslock, numlock, or scrolllock.
