JW,
Thanks!!! This code ran great!!! I wasn't familiar with the Auto_Open()
and Auto_Close() procedures and that is what I was needing to get the KB code
to work. I did, however copy all of your code into another 'test' Workbook
and it ran great. I do intend to use your code but it is good to know
another alternative as well. Thanks again.
Regards,
rw


"JW" wrote:

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 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 -