Trigger CAPS LOCK ON When Wk Bk is Opened?

[rwjack]

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]

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

 

[JW]

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]

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

 

[rwjack]

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

 

[Dave Peterson]

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

 

[JW]

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

 

[rwjack]

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

 

[rwjack]

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

 

[Otto]

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