Toggling the NUMLOCK when using SendKeys

Zakynthos · 07-21-2005, 11:05 AM

I've tried, without success, to do this in a variety of ways when attempting
to send multiple SendKey commands from Excel to an Oracle-based database
called 'Vantage Point' running in Windows.

The code I used on a UserForm1 was as follows: (it will be obvious that I
tried to adapt it from 'CAPSLOCK toggle, by the comments I left unchanged)

Private Sub UserForm_Click()

Private Sub Text1_Click()
SendKeys "1"
SendKeys "{NUMLOCK}" ' Turn on the CapsLock
SendKeys "1" ' will be printed in caps
For i = 0 To 10
SendKeys Chr(Asc("1") + i) 'will be printed in caps
Next
'DoEvents ' uncommenting this will print next line in smalls
SendKeys "o" ' will be printed in caps
SendKeys "{NUMLOCK}" ' Turn off the caps lock
SendKeys "o" ' Will be printed in smalls
End Sub

I used the following code on 'Sheet1' of my Excel workbook:

Sub inputvpco()

AppActivate "Maintain Queue Actuals"
With ThisWorkbook.Sheets("Sheet1")
SendKeys .Range("A1").Value
End With

End Sub

If the NUM LOCK is turned off, this does not turn it back on again.

I've also tried another test of this toggle using the following method
(which I'm pretty sure I followed correctly)

Start a new Standard EXE project in Visual Basic. Form1 is created by
default.
Add a CommandButton to Form1.
Copy the following code to the Code window of Form1:
Option Explicit
Private Sub Command1_Click()
SendKeys "a"
SendKeys "b"
End Sub
On the Run menu, click Start or press the F5 key to start the program.
If the NumLock light is off, turn on the NumLock light by pressing the
NumLock key. Click the CommandButton and note that the NumLock light
turns off.
Close Visual Basic and repeat the steps above; this time adding
DoEvents, as follows:
Private Sub Command1_Click()
SendKeys "a"
DoEvents
SendKeys "b"
End Sub

Again, the first half of this failed to toggle an 'on' num lock light to
off!!!!

Where do I go from here? I really do need to solve this problem of sending
data in this way, it must be possible, and I've replicated the process from
Excel to other non Microsoft 3rd Party programs running in Windows.

K Dales · 07-21-2005, 01:05 PM

Is there a reason for NumLock to be on other than sending only upper case
characters? If you just need to send upper case only, you can use the UCase
function to convert the string to upper case when you do the SendKeys:
SendKeys "1"
' Note the below has no real effect but included since you had it:
SendKeys UCase("1") ' will be printed in caps
For i = 0 To 10
SendKeys UCase(Chr(Asc("1") + i)) 'will be printed in caps
Next
'DoEvents ' uncommenting this will print next line in smalls
' Note: DoEvents only tells Excel to pause here for a moment to allow other
process to occur; it will not to anything on its own to change the CapsLock
state
SendKeys UCase("o") ' will be printed in caps
SendKeys "o" ' Will be printed in smalls

If for some reason I am not aware of it is important not only that these be
caps but that the CapsLock is actually on, that would involve Windows API
calls: too much info to cover here but here is a reference:
http://support.microsoft.com/default...b;en-us;190000
--
- K Dales

"Zakynthos" wrote:

> I've tried, without success, to do this in a variety of ways when attempting
> to send multiple SendKey commands from Excel to an Oracle-based database
> called 'Vantage Point' running in Windows.
>
> The code I used on a UserForm1 was as follows: (it will be obvious that I
> tried to adapt it from 'CAPSLOCK toggle, by the comments I left unchanged)
>
> Private Sub UserForm_Click()
>
> Private Sub Text1_Click()
> SendKeys "1"
> SendKeys "{NUMLOCK}" ' Turn on the CapsLock
> SendKeys "1" ' will be printed in caps
> For i = 0 To 10
> SendKeys Chr(Asc("1") + i) 'will be printed in caps
> Next
> 'DoEvents ' uncommenting this will print next line in smalls
> SendKeys "o" ' will be printed in caps
> SendKeys "{NUMLOCK}" ' Turn off the caps lock
> SendKeys "o" ' Will be printed in smalls
> End Sub
>
>
> I used the following code on 'Sheet1' of my Excel workbook:
>
> Sub inputvpco()
>
> AppActivate "Maintain Queue Actuals"
> With ThisWorkbook.Sheets("Sheet1")
> SendKeys .Range("A1").Value
> End With
>
> End Sub
>
>
>
> If the NUM LOCK is turned off, this does not turn it back on again.
>
> I've also tried another test of this toggle using the following method
> (which I'm pretty sure I followed correctly)
>
> Start a new Standard EXE project in Visual Basic. Form1 is created by
> default.
> Add a CommandButton to Form1.
> Copy the following code to the Code window of Form1:
> Option Explicit
> Private Sub Command1_Click()
> SendKeys "a"
> SendKeys "b"
> End Sub
> On the Run menu, click Start or press the F5 key to start the program.
> If the NumLock light is off, turn on the NumLock light by pressing the
> NumLock key. Click the CommandButton and note that the NumLock light
> turns off.
> Close Visual Basic and repeat the steps above; this time adding
> DoEvents, as follows:
> Private Sub Command1_Click()
> SendKeys "a"
> DoEvents
> SendKeys "b"
> End Sub
>
>
> Again, the first half of this failed to toggle an 'on' num lock light to
> off!!!!
>
> Where do I go from here? I really do need to solve this problem of sending
> data in this way, it must be possible, and I've replicated the process from
> Excel to other non Microsoft 3rd Party programs running in Windows.
>

K Dales · 07-21-2005, 02:05 PM

Sorry, reread it and realized you wanted NumLock; but the same issue is true:
if you just need to send the numbers, use your code to specify that. I am
not sure why else having the NumLock key "On" would affect your database
unless Oracle uses it for some proprietary purpose? (I have never used
Oracle). I also realized that you can indeed set Numlock or Capslock with
SendKeys but I had never used that before since I have had no need for it.
Sorry for the misdirection, combination of tryng to answer questions quickly
on break time plus faulty brain circuits!
--
- K Dales

"K Dales" wrote:

> Is there a reason for NumLock to be on other than sending only upper case
> characters? If you just need to send upper case only, you can use the UCase
> function to convert the string to upper case when you do the SendKeys:
> SendKeys "1"
> ' Note the below has no real effect but included since you had it:
> SendKeys UCase("1") ' will be printed in caps
> For i = 0 To 10
> SendKeys UCase(Chr(Asc("1") + i)) 'will be printed in caps
> Next
> 'DoEvents ' uncommenting this will print next line in smalls
> ' Note: DoEvents only tells Excel to pause here for a moment to allow other
> process to occur; it will not to anything on its own to change the CapsLock
> state
> SendKeys UCase("o") ' will be printed in caps
> SendKeys "o" ' Will be printed in smalls
>
> If for some reason I am not aware of it is important not only that these be
> caps but that the CapsLock is actually on, that would involve Windows API
> calls: too much info to cover here but here is a reference:
> http://support.microsoft.com/default...b;en-us;190000
> --
> - K Dales
>
>
> "Zakynthos" wrote:
>
> > I've tried, without success, to do this in a variety of ways when attempting
> > to send multiple SendKey commands from Excel to an Oracle-based database
> > called 'Vantage Point' running in Windows.
> >
> > The code I used on a UserForm1 was as follows: (it will be obvious that I
> > tried to adapt it from 'CAPSLOCK toggle, by the comments I left unchanged)
> >
> > Private Sub UserForm_Click()
> >
> > Private Sub Text1_Click()
> > SendKeys "1"
> > SendKeys "{NUMLOCK}" ' Turn on the CapsLock
> > SendKeys "1" ' will be printed in caps
> > For i = 0 To 10
> > SendKeys Chr(Asc("1") + i) 'will be printed in caps
> > Next
> > 'DoEvents ' uncommenting this will print next line in smalls
> > SendKeys "o" ' will be printed in caps
> > SendKeys "{NUMLOCK}" ' Turn off the caps lock
> > SendKeys "o" ' Will be printed in smalls
> > End Sub
> >
> >
> > I used the following code on 'Sheet1' of my Excel workbook:
> >
> > Sub inputvpco()
> >
> > AppActivate "Maintain Queue Actuals"
> > With ThisWorkbook.Sheets("Sheet1")
> > SendKeys .Range("A1").Value
> > End With
> >
> > End Sub
> >
> >
> >
> > If the NUM LOCK is turned off, this does not turn it back on again.
> >
> > I've also tried another test of this toggle using the following method
> > (which I'm pretty sure I followed correctly)
> >
> > Start a new Standard EXE project in Visual Basic. Form1 is created by
> > default.
> > Add a CommandButton to Form1.
> > Copy the following code to the Code window of Form1:
> > Option Explicit
> > Private Sub Command1_Click()
> > SendKeys "a"
> > SendKeys "b"
> > End Sub
> > On the Run menu, click Start or press the F5 key to start the program.
> > If the NumLock light is off, turn on the NumLock light by pressing the
> > NumLock key. Click the CommandButton and note that the NumLock light
> > turns off.
> > Close Visual Basic and repeat the steps above; this time adding
> > DoEvents, as follows:
> > Private Sub Command1_Click()
> > SendKeys "a"
> > DoEvents
> > SendKeys "b"
> > End Sub
> >
> >
> > Again, the first half of this failed to toggle an 'on' num lock light to
> > off!!!!
> >
> > Where do I go from here? I really do need to solve this problem of sending
> > data in this way, it must be possible, and I've replicated the process from
> > Excel to other non Microsoft 3rd Party programs running in Windows.
> >

Tim Williams · 07-21-2005, 07:05 PM

Try using (eg)

SendKeys "1", True

second parameter means wait until the keystrokes have been processed.

--
Tim Williams
Palo Alto, CA

"Zakynthos" <Zakynthos@discussions.microsoft.com> wrote in message
news:B7EC02F9-7021-40C5-9F6F-E0AC0A54B614@microsoft.com...
> I've tried, without success, to do this in a variety of ways when
attempting
> to send multiple SendKey commands from Excel to an Oracle-based database
> called 'Vantage Point' running in Windows.
>
> The code I used on a UserForm1 was as follows: (it will be obvious that I
> tried to adapt it from 'CAPSLOCK toggle, by the comments I left unchanged)
>
> Private Sub UserForm_Click()
>
> Private Sub Text1_Click()
> SendKeys "1"
> SendKeys "{NUMLOCK}" ' Turn on the CapsLock
> SendKeys "1" ' will be printed in caps
> For i = 0 To 10
> SendKeys Chr(Asc("1") + i) 'will be printed in caps
> Next
> 'DoEvents ' uncommenting this will print next line in smalls
> SendKeys "o" ' will be printed in caps
> SendKeys "{NUMLOCK}" ' Turn off the caps lock
> SendKeys "o" ' Will be printed in smalls
> End Sub
>
>
> I used the following code on 'Sheet1' of my Excel workbook:
>
> Sub inputvpco()
>
> AppActivate "Maintain Queue Actuals"
> With ThisWorkbook.Sheets("Sheet1")
> SendKeys .Range("A1").Value
> End With
>
> End Sub
>
>
>
> If the NUM LOCK is turned off, this does not turn it back on again.
>
> I've also tried another test of this toggle using the following method
> (which I'm pretty sure I followed correctly)
>
> Start a new Standard EXE project in Visual Basic. Form1 is created by
> default.
> Add a CommandButton to Form1.
> Copy the following code to the Code window of Form1:
> Option Explicit
> Private Sub Command1_Click()
> SendKeys "a"
> SendKeys "b"
> End Sub
> On the Run menu, click Start or press the F5 key to start the program.
> If the NumLock light is off, turn on the NumLock light by pressing the
> NumLock key. Click the CommandButton and note that the NumLock light
> turns off.
> Close Visual Basic and repeat the steps above; this time adding
> DoEvents, as follows:
> Private Sub Command1_Click()
> SendKeys "a"
> DoEvents
> SendKeys "b"
> End Sub
>
>
> Again, the first half of this failed to toggle an 'on' num lock light to
> off!!!!
>
> Where do I go from here? I really do need to solve this problem of
sending
> data in this way, it must be possible, and I've replicated the process
from
> Excel to other non Microsoft 3rd Party programs running in Windows.
>

**Alex17** · 10-08-2013, 01:38 PM

Several years old - but after searching for about 30 minutes, this was the answer that finally worked.

Thank you Tim!

SendKeys "1", True
It works!

**sashibyd** · 09-20-2017, 02:45 AM

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 Excel Sheet's Code (NOT Module) (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. Sashi Elit

**alansidman** · 09-20-2017, 04:14 AM

@sashibyd

It is highly unlikely that the OP in this case is still interested in this post. It dates back to 2005. Welcome to the forum. I suggest if you wish to help that you work on current issues. Twelve years is a long time to wait for an answer, even on this forum.