Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CAPS LOCK (2007)

    I have a spreadsheet with a macro which runs automatically when I open it and makes the gridlines invisible, goes into full screen and the like; and a QUIT macro which reverses all that.

    Are there lines I could add which would turn the Caps Lock key on and off. For reasons I won't go into I want to run the spreadsheet in capital letters.

    Thanks, all.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CAPS LOCK (2007)

    Place the following declaration at the top of the module:

    Private Declare Function GetKeyState Lib "user32" _
    (ByVal nVirtKey As Long) As Integer

    The following code can go anywhere below the declarations at the top:

    Function IsCapsOn() As Boolean
    IsCapsOn = GetKeyState(vbKeyCapital)
    End Function

    Sub SetCapsLock(OnOff As Boolean)
    If OnOff Xor IsCapsOn Then
    SendKeys "{CAPSLOCK}"
    End If
    End Sub

    You can use the IsCapsOn function to inspect the current state of Caps Lock.

    To turn Caps Lock on:

    SetCapsLock True

    To turn it off:

    SetCapsLock False

    To toggle it, you can simply use

    SendKeys "{CAPSLOCK}"

  3. #3
    New Lounger
    Join Date
    Sep 2005
    Location
    Derbyshire, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CAPS LOCK (2007)

    Here's another way, more code but it avoids SENDKEYS

    Option Explicit

    Private Const VER_PLATFORM_WIN32_NT = 2
    Private Const VER_PLATFORM_WIN32_WINDOWS = 1
    Private Const VK_CAPITAL = &H14
    Private Const KEYEVENTF_EXTENDEDKEY = &H1
    Private Const KEYEVENTF_KEYUP = &H2


    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

    Public Sub ToggleCapsLock(bTurnOn As Boolean)

    'To turn capslock on, set bTurnOn to true
    'To turn capslock off, set bTurnOn to false

    Dim bytKeys(255) As Byte
    Dim bCapsLockOn As Boolean

    'Get status of the 256 virtual keys
    GetKeyboardState bytKeys(0)

    bCapsLockOn = bytKeys(VK_CAPITAL)
    Dim typOS As OSVERSIONINFO

    If bCapsLockOn <> bTurnOn Then 'if current state <>
    'requested state

    If typOS.dwPlatformId = _
    VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98

    bytKeys(VK_CAPITAL) = 1
    SetKeyboardState bytKeys(0)

    Else '=== WinNT/2000

    '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

    Sub switchOn()
    ToggleCapsLock (True)
    End Sub

    Sub switchOff()
    ToggleCapsLock (False)
    End Sub

  4. #4
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CAPS LOCK (2007)

    Fantastic, and many thanks to you both.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CAPS LOCK (2007)

    Thanks, that's useful if you want to avoid SendKeys.

Posting Permissions

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