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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
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.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636

ADVERTISEMENT

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
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
I'm confused as to why some of the people here don't utilize the windows API ?
 

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075

ADVERTISEMENT

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
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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.
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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!
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
Microsoft isn't publisher of documentation regarding the win32 api :) The internet can be a great resource :)
 

Forum statistics

Threads
1,144,451
Messages
5,724,425
Members
422,553
Latest member
excelgraham

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
Top