Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Capslock in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,318
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,597
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ~Anne Troy

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    JSW: Try and try again: "The way of the Coder!"

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,318
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  6. #6
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm confused as to why some of the people here don't utilize the windows API ?

  7. #7
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default

    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



  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,318
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm confused as to why some of the people here don't utilize the windows API ?
    DUDE! Because it's frickin' confusing! lol!

    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!

  10. #10
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Microsoft isn't publisher of documentation regarding the win32 api The internet can be a great resource

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •