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

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.

You can use a couple of nicely named procedures in a General module:

Sub Auto_Open()
call TurnSettingsOn
End Sub

Sub Auto_Close()
call TurnSettingsOff
End Sub

Or you could use a couple of events under the ThisWorkbook module:

Private Sub Workbook_Open()
call TurnSettingsOn
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
call TurnSettingsOff
End Sub

=======
I hope this is just for you. I'd hate your workbook taking over my keyboard!
.

I'm sorry but I don't have enough information. I copied in the following
code from the KB and it does exactly what I want it to but I have to fire it
off from the VBA Code module or change 'Private' to 'Public' and fire it off
from a button on the first Worksheet of the Workbook. I don't have the
programming knowledge that you or JW have so I need a little more
clarification if you would be so kind as to go into a little more detail.

Here is my code:
' This code in the Declarations and the following
' sub procedure came from the Microsoft Article ID: 177674 entitled
' How To Toggle the NUM LOCK, CAPS LOCK, and SCROLL LOCK Keys.
' It was found at http://support.microsoft.com/kb/177674/EN-US/
' This code also works in Windows XP

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:

All this goes into a General Module:

Option Explicit

' Declare Type for API call:
Private Type OSVERSIONINFO
dwOSVersionInfoSize As Long
dwMajorVersion As Long
dwMinorVersion As Long
dwBuildNumber As Long
dwPlatformId As Long
szCSDVersion As String * 128 ' Maintenance string for PSS usage
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)

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

Dave,
Thank you!!! The Auto_Open and Auto_Close procedures are exactly what I
needed. The code works great now!!
Regards,
rw

"Dave Peterson" wrote:

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:

"rwjack" wrote: