> I prefer this code over the one provided by the Knowledge Base. It
> uses a simple sub to control the setting of the caps lock key through
> the use of a boolean variable.
>
> The way I would handle it would be to first capture the state of the
> caps lock key when the workbook is initially opened and store it in a
> variable. Then, turn on caps lock. When closing the workbook,
> instead of automatically turning caps lock off, set the caps lock key
> equal to whatever it was when the workbook was first opened.
>
> Place all of this in a regular module
> Option Explicit
>
> Dim bytKeys(255) As Byte
> Dim capsState As Boolean
>
> 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(turnOn As Boolean)
> 'To turn capslock on, set turnon to true
> 'To turn capslock off, set turnon to false
> Dim bCapsLockOn As Boolean
> 'Get status of the 256 virtual keys
> GetKeyboardState bytKeys(0)
> bCapsLockOn = bytKeys(VK_CAPITAL)
> Dim typOS As OSVERSIONINFO
> If bCapsLockOn <> turnOn Then 'if current state <>
> 'requested stae
> 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 Auto_Open()
> GetKeyboardState bytKeys(0)
> capsState = bytKeys(VK_CAPITAL)
> If capsState <> True Then ToggleCapsLock True
> End Sub
>
> Sub Auto_Close()
> ToggleCapsLock capsState
> End Sub
>
>
> On May 6, 5:55 pm, rwjack discussions.microsoft.com> wrote:

>> JW,
>> Please read my reply to Dave Peterson. I greatly appreciate your input but
>> it's not enough information for me to make it work. If you could go into a
>> little more detail that would be great!
>>
>> Thanks,
>> rw
>>
>>
>>
>> "JW" wrote:

>>> Have a look here. Should do exactly what you want.
>>>http://www.freevbcode.com/ShowCode.Asp?ID=1004

>>

>>> Using that code, you could then do this in the Workbook module:

>>

>>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>>> ToggleCapsLock False
>>> End Sub

>>

>>> Private Sub Workbook_Open()
>>> ToggleCapsLock True
>>> End Sub

>>

>>> You could also store the value of the caps lock key into a global
>>> variable when opening the workbook and set it back to that value when
>>> closing the workbook.

>>

>>> rwjack wrote:

>>>> How do you trigger an event when opening a particular Excel Workbook?

>>

>>>> Tom Ogilvy responded to a question on how to check and set NUM LOCK, CAPS
>>>> LOCK and SCROLL LOCK on (or off) by refering to Microsoft Knowledge Base
>>>> Article 177674. I copied all the code from this article into a VBA module,
>>>> having a Windows XP operating system and it worked beautifully!

>>

>>>> I would like to trigger and event to turn on NUM LOCK and CAPS LOCK when a
>>>> Workbook is Opened and turn CAPS LOCK off when the Workbook is closed.

>>

>>>> Thanks,
>>>> rw- Hide quoted text -

>>
>> - Show quoted text -

>
>