Capslock in VBA

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Hey all,

How do you programatically enable the CAPSLOCK key in VBA? I've tried:

Sendkeys "{CAPSLOCK}"

When I run this, Excel's status bar flashes "CAPS" for about 1 second then disappears. It seems to have no impact on the keyboard for case-sensitivity - which is what I'm shooting for.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Ok, so WHEN or WHERE do you want the cap locks on?

Generally, we use a worksheet event on a specific range, which forces the entries to change to upper case.
 
Upvote 0
Here are three ways of doing it:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Dim rng As Range
'Make all to be entered text Caps.
'Load from ThisWorkBook Module.
With Application
.EnableEvents = False
For Each rng In Source
rng.Value = UCase(rng.Value)
Next
.EnableEvents = True
End With
End Sub

Sub MakeUpper()
'Change the Range below to the working Range to use!
'Sheet module code.
'Work on a range only.
For Each c In Worksheets("Sheet1").Range("A3:AA3")
c.Value = UCase(c.Value)
Next c
End Sub

Sub MakeSelectUC()
'Sheet module code.
'Work on a selection only.
For Each c In ActiveCell.CurrentRegion.Cells
Selection.Value = UCase(c.Value)
Next c
End Sub
 
Upvote 0
Hey Dreamboat!

I was hoping I could enable the CAPSLOCK in a Workbook_Open Event - just wasn't sure if I was using the SendKeys code correctly. I may go with the UCase on a Worksheet_Change event like you were saying. Just trying to learn more stuff :)

Thanks!
Adam

Edit (just read Joe's Post).
Joe, you're code looks like the direction to go. Many thanks!
This message was edited by Asala42 on 2002-11-01 14:28
 
Upvote 0
You can, do this:

General decs:

Private Type OSVERSIONINFO
dwOSVersionInfoSize As Long
dwMajorVersion As Long
dwMinorVersion As Long
dwBuildNumber As Long
dwPlatformId As Long
szCSDVersion As String * 128 '
End Type

' API declarations:

Private Declare Function GetVersionEx Lib "kernel32" _
Alias "GetVersionExA" _
(lpVersionInformation As OSVERSIONINFO) As Long

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

Private Declare Function GetKeyboardState Lib "user32" _
(pbKeyState As Byte) As Long

Private Declare Function SetKeyboardState Lib "user32" _
(lppbKeyState As Byte) As Long

' Constant declarations:
Const VK_CAPITAL = &H14
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2
Const VER_PLATFORM_WIN32_NT = 2
Const VER_PLATFORM_WIN32_WINDOWS = 1

Private Sub Workbook_Open()
Dim CapsLockState As Boolean
Dim o As OSVERSIONINFO


o.dwOSVersionInfoSize = Len(o)
GetVersionEx o
Dim keys(0 To 255) As Byte
GetKeyboardState keys(0)

CapsLockState = keys(VK_CAPITAL)
If CapsLockState <> True Then 'Turn capslock on
If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98
keys(VK_CAPITAL) = 1
SetKeyboardState keys(0)
ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then '=== WinNT
'Simulate Key Press
keybd_event VK_CAPITAL, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'Simulate Key Release
keybd_event VK_CAPITAL, &H45, KEYEVENTF_EXTENDEDKEY _
Or KEYEVENTF_KEYUP, 0
End If
End If
End Sub
 
Upvote 0
I'm confused as to why some of the people here don't utilize the windows API ?
 
Upvote 0
Another way to handle the problem;

Private Type KeyboardBytes
kbByte(0 To 255) As Byte
End Type
Dim kbArray As KeyboardBytes

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long
Private Declare Function GetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long
Private Declare Function SetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long


Private Sub Workbook_Open()
KeyCode = &H14
GetKeyboardState kbArray
kbArray.kbByte(KeyCode) = IIf(kbArray.kbByte(KeyCode) = 1, 0, 1)
SetKeyboardState kbArray
End Sub
 
Upvote 0
On 2002-11-01 14:39, zacemmel wrote:
I'm confused as to why some of the people here don't utilize the windows API ?

Probably because Microsoft does a really good job of documenting them. W@nkers.
 
Upvote 0
I'm confused as to why some of the people here don't utilize the windows API ?

DUDE! Because it's frickin' confusing! lol! :biggrin:

Ok I'm just kidding. Seriously, I'm checking out your code now- Some sweet stuff there that I just don't usually play with. Thanks!
 
Upvote 0
Microsoft isn't publisher of documentation regarding the win32 api :) The internet can be a great resource :)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top