Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger CAPS LOCK ON When Wk Bk is Opened?
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to lock the Ctrl key? (as locking the Shift key w/Caps Lock) | Excel Discussion (Misc queries) | |||
auto caps lock | Excel Discussion (Misc queries) | |||
How can Excel vba detect if Caps Lock or Num Lock is on? | Excel Programming | |||
Caps Lock | Excel Programming | |||
Caps lock | Excel Programming |