MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Num Lock key


Posted by Ian Bartlett on January 22, 2002 5:29 AM

Is it possible for Excel to display the status of the Num Lock key, and - more useful - to change it's status if it's in the 'wrong' state (ie. switch it from off to on)?

Many thanks,

Ian


Posted by Mark W. on January 22, 2002 8:46 AM

Excel does display the numeric lock on the
Status Bar.

Posted by Ian Bartlett on January 22, 2002 11:11 AM

Thanks, Mark,

Is it possible to pick the value from the status bar, and get it into a cell in a worksheet? I'm trying to alert users who may not spot the status bar flag - if num lock is off, I'd like to (for instance) change the colour of an area on the spreadsheet.

Better still, can I use a macro to ensure that it's switched on?

TIA,

Ian

Posted by Juan Pablo G. on January 22, 2002 11:16 AM

Copy this to a regular module.

Public Const VK_NUMLOCK = &H90

Public Declare Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Long


Sub Test()
MsgBox "Numlock is " & IIf(GetKeyState(VK_NUMLOCK) = 1, "On", "Off")
End Sub

The Test will say if NumLock is On or Off. I couldn't get the other API function i found to work, maybe the problem is with the Keyboard. But this may help you.

Juan Pablo G. Thanks, Mark, Is it possible to pick the value from the status bar, and get it into a cell in a worksheet? I'm trying to alert users who may not spot the status bar flag - if num lock is off, I'd like to (for instance) change the colour of an area on the spreadsheet. Better still, can I use a macro to ensure that it's switched on? TIA,

Posted by Juan Pablo G. on January 22, 2002 3:07 PM

Finally got it to work. I think it was the keyboard issue. Copy to a regular module. Run Test sub to see if it works on your computer (You should see lights turning on and off, NumLock, Caps and Scroll)

Const VK_CAPITAL = &H14
Const VK_NUMLOCK = &H90
Const VK_SCROLL = &H91
Const VK_USED = VK_SCROLL
Private Type KeyboardBytes
kbByte(0 To 255) As Byte
End Type
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long
Private Declare Function GetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long
Private Declare Function SetKeyboardState Lib "user32" (kbArray As KeyboardBytes) As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim kbArray As KeyboardBytes, CapsLock As Boolean, kbOld As KeyboardBytes

Sub Test()
For i = 1 To 10
If GetKeyState(VK_NUMLOCK) = 1 Then
TurnOff VK_NUMLOCK
TurnOn VK_CAPITAL
TurnOff VK_SCROLL
Else
TurnOn VK_NUMLOCK
TurnOff VK_CAPITAL
TurnOn VK_SCROLL
End If
Sleep 100
Next i
End Sub

Private Sub TurnOn(vkKey As Long)
'Get the keyboard state
GetKeyboardState kbArray
'Change a key
kbArray.kbByte(vkKey) = 1
'Set the keyboard state
SetKeyboardState kbArray
End Sub
Private Sub TurnOff(vkKey As Long)
'Get the keyboard state
GetKeyboardState kbArray
'change a key
kbArray.kbByte(vkKey) = 0
'set the keyboard state
SetKeyboardState kbArray
End Sub

Juan Pablo G. Copy this to a regular module. Public Const VK_NUMLOCK = &H90 Public Declare Function GetKeyState Lib "user32" _

Posted by Ian Bartlett on January 23, 2002 7:09 AM

Thanks, Juan Pablo,

That did the trick. I used the GetKeyState(VK_NUMLOCK) line, pulled the value into a named range on he spreadsheet and took it from there. I tried the second block of code that you sent, but that confused the keyboard - the lights didn't change, Excel showed a NUM flag on the statusbar, but the keypad still acted as navigation arrows. Anyway, problem solved.

When I see code like that, I realise how much I still have to learn...

Thanks again,

Ian