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:

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)

Private Declare Function GetKeyboardState Lib "user32" _
(pbKeyState As Byte) As Long

Private Declare Function SetKeyboardState Lib "user32" _
(lppbKeyState As Byte) As Long

' Constant declarations:
Const VK_NUMLOCK = &H90
Const VK_SCROLL = &H91
Const VK_CAPITAL = &H14
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2
Const VER_PLATFORM_WIN32_NT = 2
Const VER_PLATFORM_WIN32_WINDOWS = 1

Public Sub CapsOn()

Dim o As OSVERSIONINFO
Dim NumLockState As Boolean
Dim ScrollLockState As Boolean
Dim CapsLockState As Boolean

o.dwOSVersionInfoSize = Len(o)
GetVersionEx o
Dim keys(0 To 255) As Byte
GetKeyboardState keys(0)

' NumLock handling:
NumLockState = keys(VK_NUMLOCK)
If NumLockState < True Then 'Turn numlock on
If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98

keys(VK_NUMLOCK) = 1
SetKeyboardState keys(0)
ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then '=== WinNT
'Simulate Key Press
keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'Simulate Key Release
keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY _
Or KEYEVENTF_KEYUP, 0
End If
End If

' CapsLock handling:
CapsLockState = keys(VK_CAPITAL)
If CapsLockState < True Then 'Turn capslock on
If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98
keys(VK_CAPITAL) = 1
SetKeyboardState keys(0)
ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then '=== WinNT
'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

' NOT CURRENTLY USING THIS CODE
' ScrollLock handling:
' ScrollLockState = keys(VK_SCROLL)
' If ScrollLockState < True Then 'Turn Scroll lock on
' If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98
' keys(VK_SCROLL) = 1
' SetKeyboardState keys(0)
' ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then '=== WinNT
'Simulate Key Press
' keybd_event VK_SCROLL, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'Simulate Key Release
' keybd_event VK_SCROLL, &H45, KEYEVENTF_EXTENDEDKEY _
' Or KEYEVENTF_KEYUP, 0
' End If
' End If
End Sub

Sub auto_open()
Call CapsOn
End Sub

'Notice how the Auto_Open doesn't really do much--it just calls the code that
you already have working.

'Same thing with auto_Close

Sub Auto_Close()
Call CapsOff 'I guessed that this is the name of your procedure???
End sub



rwjack wrote:

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

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

Private Declare Function GetKeyboardState Lib "user32" _
(pbKeyState As Byte) As Long

Private Declare Function SetKeyboardState Lib "user32" _
(lppbKeyState As Byte) As Long

' Constant declarations:
Const VK_NUMLOCK = &H90
Const VK_SCROLL = &H91
Const VK_CAPITAL = &H14
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2
Const VER_PLATFORM_WIN32_NT = 2
Const VER_PLATFORM_WIN32_WINDOWS = 1

Public Sub CapsOn()
'Private Sub Workbook_Open() temporarily commented out.

Dim o As OSVERSIONINFO
Dim NumLockState As Boolean
Dim ScrollLockState As Boolean
Dim CapsLockState As Boolean

o.dwOSVersionInfoSize = Len(o)
GetVersionEx o
Dim keys(0 To 255) As Byte
GetKeyboardState keys(0)

' NumLock handling:
NumLockState = keys(VK_NUMLOCK)
If NumLockState < True Then 'Turn numlock on
If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98

keys(VK_NUMLOCK) = 1
SetKeyboardState keys(0)
ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then '=== WinNT
'Simulate Key Press
keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'Simulate Key Release
keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY _
Or KEYEVENTF_KEYUP, 0
End If
End If

' CapsLock handling:
CapsLockState = keys(VK_CAPITAL)
If CapsLockState < True Then 'Turn capslock on
If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98
keys(VK_CAPITAL) = 1
SetKeyboardState keys(0)
ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then '=== WinNT
'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

' NOT CURRENTLY USING THIS CODE
' ScrollLock handling:
' ScrollLockState = keys(VK_SCROLL)
' If ScrollLockState < True Then 'Turn Scroll lock on
' If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98
' keys(VK_SCROLL) = 1
' SetKeyboardState keys(0)
' ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then '=== WinNT
'Simulate Key Press
' keybd_event VK_SCROLL, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'Simulate Key Release
' keybd_event VK_SCROLL, &H45, KEYEVENTF_EXTENDEDKEY _
' Or KEYEVENTF_KEYUP, 0
' End If
' End If
End Sub

I will greatly appreciate any additional information you provide!

Thanks,
rw

"Dave Peterson" wrote:

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!
<vbg.

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

--

Dave Peterson


--

Dave Peterson