Checking for Numlock status, fixing Numlock status

Question

Office 2010 on Windows 7 Enterprise.

To monitor the call center status, I have a macro that keeps the screen from timing out by using Sendkeys to press the {home} key once every ten minutes.  It seems to be the only way to do it on this computer and it works fine.  I "break" the macro if I plan on using another Excel spreadsheet there, but most of my active spreadsheet use is on another computer.

However, the Sendkeys macro often, but not always, also turns off the numlock, which is an inconvenience in my frequent use of another (non-Excel) program.  Is there a way in the macro that I can either**:**

1. After the {home} execution, check the status of the numlock and if it's off, use Sendkeys to press the {numlock} key, or
2. Just turn the numlock on after every {home} execution?

Answer 1

Sometimes it appears to works and sometimes not.

 

I pasted the code into its own module.  In the "Keystroke" macro (in another module) I added "TurnNumLockOn" to the line after the Sendkeys statement >

 

Sub Keystroke()

For cnt = 1 To 10000

SendKeys "{home}"

TurnNumLockOn

Application.Wait (Now + TimeValue("0:10:00"))

Next

End Sub

 

Whether or not the focus stays on the Keystroke macro spreadsheet, the computer screen does not time out, so that part is working.  But I still have a problem with the numlock sometimes turning off and sometimes turning on.  Turning on is good, but why the inconsistency?

 

Note that the Keystroke macro spreadsheet does not always have the focus, since I might be actively using other apps, or might just be monitoring them.  Heck, I have even purposely turned off the numlock, then started the macro, and it has sometimes turned it back on immediately and sometimes not.

 

Any ideas?

 

 

Try changing this line of code....

SendKeys "{home}"

to this....

SendKeys "{home}", True

That second optional argument will make the macro wait until the SendKeys operation has completed. I believe the "sometimes it appears to works and sometimes not" is probably due to a timing issue.... if I am right, then making the macro wait until SendKeys is done should fix the intermittent problem you are seeing.

Answer 2

Sometimes it appears to works and sometimes not.

I pasted the code into its own module.  In the "Keystroke" macro (in another module) I added "TurnNumLockOn" to the line after the Sendkeys statement >

Sub Keystroke()

For cnt = 1 To 10000

SendKeys "{home}"

TurnNumLockOn

Application.Wait (Now + TimeValue("0:10:00"))

Next

End Sub

Whether or not the focus stays on the Keystroke macro spreadsheet, the computer screen does not time out, so that part is working.  But I still have a problem with the numlock sometimes turning off and sometimes turning on.  Turning on is good, but why the inconsistency?

Note that the Keystroke macro spreadsheet does not always have the focus, since I might be actively using other apps, or might just be monitoring them.  Heck, I have even purposely turned off the numlock, then started the macro, and it has sometimes turned it back on immediately and sometimes not.

Any ideas?

Answer 3

Copy/Paste this code (exactly as presented below) into a module (I would suggest by itself; that is, different from the one your other code is in) and then execute the TurnNumLockOn macro whenever you want to make sure the NumLock is set to the "on" setting...

Private Const VER_PLATFORM_WIN32_NT = 2

Private Const VER_PLATFORM_WIN32_WINDOWS = 1

Private Const VK_NUMLOCK = &H90

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

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 TurnNumLockOn()

  Dim bytKeys(255) As Byte, bnumLockOn As Boolean, typOS As OSVERSIONINFO

  GetKeyboardState bytKeys(0)

  bnumLockOn = bytKeys(VK_NUMLOCK)

  If Not bnumLockOn Then

    If typOS.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then

      bytKeys(VK_NUMLOCK) = 1

      SetKeyboardState bytKeys(0)

    Else

      keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0

      keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0

    End If

  End If

End Sub

Answer 4

How can mere mortals fix the problem? Macros? Not my expertise.

In my (and my coworkers) situaiton Num Lock turns off when opening Excel attachments. (We recently migrated to Windows 7, Office 2010, and I have an HP laptop and external keyboard).

Answer 5

How "True" it is**!**

That was the cure - Thanks.