Scroll lock key

[akforsyt]

Let me start off by explaining what I am trying to do.

I would like the scroll lock key to light up whenever there is something in my range and turn off when it is empty. I have the procedure below called every minute (I'm using Application.OnTime to do this). It is a small part of a much larger project. I currently have it check to see if my cells are empty by looking at a cell (A3) that contains =Counta(myRange)

The problem is that when I allow the timer to control this, the scroll led flashes on then quickly back off. When I activate the procedure manually, it works fine.

What is going on here? Is there a better way to do this?

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" (ByVal nVirtKey As Long) As Integer
Const VK_SCROLL = &H91
Declare Function SetKeyboardState Lib "user32" (kbArray As Byte) As Long
Declare Function GetKeyboardState Lib "user32" (lpKeyState As Byte) As Long
 
Sub SetKeyStateOff(cc As Integer)
 Dim KeyState(0 To 255) As Byte
 GetKeyboardState KeyState(0)
 KeyState(cc) = 1  'Change to 0 for on
 SetKeyboardState KeyState(0)
End Sub
 
Sub SCRL_inactiv()
If Sheets("TestingSchedule").Range("A3") = 0 Then
If Not (GetKeyState(VK_SCROLL) = 0) Then
keybd_event VK_SCROLL, 1, 0, 0
keybd_event VK_SCROLL, 1, KEYEVENTF_KEYUP, 0
SetKeyStateOff VK_SCROLL
End If
End If
End Sub
 
Sub SCRl_activ()
If Sheets("TestingSchedule").Range("A3").Value > 0 Then
If Not (GetKeyState(VK_SCROLL) = 1) Then
keybd_event VK_SCROLL, 1, 0, 0
keybd_event VK_SCROLL, 1, KEYEVENTF_KEYUP, 0
End If
End If
End Sub

 

[akforsyt]

Re: Scroll lock key led

ok. i have made some progress but still not working quite right. Again A3 holds =Counta(myRange)

This works well for turning on the Scroll Lock LED when myRange has something in it.
The problem is that it will constantly turn off and back on when the range is empty.


Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" (ByVal nVirtKey As Long) As Integer
Const VK_SCROLL = &H91
Declare Function SetKeyboardState Lib "user32" (kbArray As Byte) As Long
Declare Function GetKeyboardState Lib "user32" (lpKeyState As Byte) As Long

Sub SetKeyStateOff(cc As Integer)
Dim KeyState(0 To 255) As Byte
GetKeyboardState KeyState(0)
KeyState(cc) = 0 'Change to 1 for on
SetKeyboardState KeyState(0)
End Sub

Sub SCRL_activ()
'Active
Range("A4").Value = GetKeyState(VK_SCROLL)
If Sheets("TestingSchedule").Range("A3") > 0 Then
If Sheets("TestingSchedule").Range("A4") = 0 Then
Set WshShell = CreateObject("WScript.Shell")
WshShell.SendKeys "{SCROLLLOCK}"
End If
End If

'Inactive
Range("A4").Value = GetKeyState(VK_SCROLL)
If Sheets("TestingSchedule").Range("A3").Value = 0 Then
If Sheets("TestingSchedule").Range("A4").Value = 1 Then
Set WshShell = CreateObject("WScript.Shell")
WshShell.SendKeys "{SCROLLLOCK}"
SetKeyStateOff VK_SCROLL
End If
End If
End Sub