Macro turning off Number Lock

[lemeister]

I have run the following piece of code numerous times a day for the past few years without any problems (it basically uses the send keys to run an excel add in to extract data from an AS400 database).
However, we recently upgraded to excel 2007, and now each time I run the code, it turns off the number lock. I have no idea why it is doing this. Could it be the sequence of sendkey commands or something else?? I'm very confused and it's getting a little frustrating!

Private Sub CommandButton1_Click()
With Range("TimeStamp")
    .Select
    .FormulaR1C1 = "=now()"
    .Copy
    .PasteSpecial xlPasteValues
End With

Range("User").Value = Environ("Username")

Range("DataStart:$CC$20000").ClearContents
Range("DataStart").Select

SendKeys "%{x}"
SendKeys "+{r}"
SendKeys "+{n}"
SendKeys "{ENTER}"
SendKeys "EQUATORRE-PROD.QBE.EO"
SendKeys "{ENTER}"
SendKeys Range("Queryname").Value
SendKeys "{ENTER}"
SendKeys "{ENTER}"
SendKeys "{ENTER}"
End Sub

 

[BrianB]

I use Sendkeys quite a lot but am not familiar with your code like
SendKeys "%{x}". What does this do ? It may be the cause of the problem.

Curly brackets normally mean that a special key is required like your lower code .. SendKeys "{ENTER}"

 

[lemeister]

I use Sendkeys quite a lot but am not familiar with your code like
SendKeys "%{x}". What does this do ? SendKeys "{ENTER}"

The SendKeys "%{x}" is a keyboard shortcut for Alt x - it's basiscally a shortcut to the the Add-Ins tab in Excel 2007.

 

[RoryA]

There used to be a bug with multiple sendkeys statements in a row (see this MSKB article) - perhaps it has reappeared.

 

[Kenneth Hobson]

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 VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub test()
 'NUM_Off
 NUM_On
End Sub

Sub NUM_TOGGLE()
  'Toggle NUM-Lock key state
  keybd_event VK_NUMLOCK, 1, 0, 0
  keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub

Sub NUM_On()  'Turn NUM-Lock on
  If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
  End If
End Sub

Sub NUM_Off() 'Turn NUM-Lock off
  If (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
  End If
End Sub

 

[lemeister]

Thanks folks, that has shed a little light on the issue.

That code works perfect (although I cannot confess to understanding most it!)

 

[sashibyd]

Hey There!!!

First of all, Copy and paste the following code in your Excel Sheet’s Module (Ex:-Module-1)...

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
           Private Const kCapital = 20
           Private Const kNumlock = 144
 
           Public Function CapsLock() As Boolean
           CapsLock = KeyState(kCapital)
           End Function
 
           Public Function NumLock() As Boolean
           NumLock = KeyState(kNumlock)
           End Function
 
           Private Function KeyState(lKey As Long) As Boolean
           KeyState = CBool(GetKeyState(lKey))
           End Function

Then, Copy and Paste the following in your Sheet's Code (Ex:- Sheet1 (Code)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
          Range("XFD1").FormulaR1C1 = "=NumLock()"
          If Range("XFD1").Value = "FALSE" Then
          SendKeys "{NUMLOCK}"
          Else
          End If
          End Sub

Now Chill!!! For Each SelectionChange you make, Excel Refreshes itself and It makes sure that Numlock is On Always.
Replace

Capslock

instead of

Numlock

if you need it so as the case may be.


Thanks. Elit