Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: CAPS LOCK ON WHEN SHEET IS OPEN

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is the VB that will turn the CAPS LOCK on when a sheet is opened?

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 18:15, Eddie G. wrote:
    Is the VB that will turn the CAPS LOCK on when a sheet is opened?
    In the Thisworkbook object place this code;

    Private Sub Workbook_Open()
    Caps_On
    End Sub


    In A Std module place this code

    Option Explicit
    Private Const VK_CAPITAL = &H14

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

    Private 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

    Sub Caps_On()
    GetKeyboardState kbArray
    kbArray.kbByte(VK_CAPITAL) = 1
    SetKeyboardState kbArray
    MsgBox "On"
    End Sub


    Sub Caps_Off()
    GetKeyboardState kbArray
    kbArray.kbByte(VK_CAPITAL) = 0
    SetKeyboardState kbArray
    MsgBox "Off"
    End Sub


    HTH

    Ivan

  3. #3
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi Eddie. Ivan presented the perfect code which used API.
    So, I proposed the following method as another way.
    It is the method of making all letter to "capital letter" compulsorily.

    '//Please copy this into a Thisworkbook Module
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Dim rng As Range
    With Application
    .EnableEvents = False
    For Each rng In Source
    rng.Value = UCase(rng.Value)
    Next
    .EnableEvents = True
    End With
    End Sub

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thank you Colo and Ivan. Both ways work great.

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
  •