Activate Caps Lock When a Workbook Opens and When the Workbook is made Active

[CADcliff]

I want to assure all my data entry in this Excel Workbook is Uppercase. I am navigating back and forth to other programs in the process. Is there any way I can capture the current CAPS Lock status, then turn on CAPS lock when I open the workbook? I would like to be able to "restore" the captured CAPS lock when another program window is made active, and then turn CAPS lock back on when I reactivate the Excel workbook window. I found an old thread from 2004, but the instructions and code there do not seem to work for me in Windows 10 and Excel 2016. I may use this in many of my Excel workbooks if We find something that works. I have looked at the all uppercase fonts, but they are not really suitable for clean data display. Thank you for any help!

 

[Rick Rothstein]

You could consider establishing a SheetChange event procedure for the ThisWorkbook code module which would automatically upper case any value entered into a cell on any worksheet in the workbook (that way you would not have to concern yourself with the CAPS LOCK key)...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Application.EnableEvents = False
  Target.Value = UCase(Target.Value)
  Application.EnableEvents = True
End Sub

 

[CADcliff]

Thanks Rick. That is a good suggestion. I am currently using something similar.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.HasFormula Then Exit Sub
        Application.EnableEvents = False
        Target = UCase(Target.Cells(1))
    Application.EnableEvents = True
End Sub

I guess I'm a little OCD about data entry. I wanted to set up the Excel sheet to help me (make me) enter the data correctly. I was frustrated to find code to turn CAPS lock off and on, but not be able to figure out how to use it.

 

[CADcliff]

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