3 digits then move the focus

  • Thread starter Thread starter mikewild2000
  • Start date Start date
M

mikewild2000

I have a little VB code in a "worksheet_change" decalration.

I am trying to add code that will move the focus to C11 once the user
has inputted 3 digits in cell J7

I hope somebody can help.

Thanks.
 
mikewild2000 > said:
I have a little VB code in a "worksheet_change" decalration.

I am trying to add code that will move the focus to C11 once the user
has inputted 3 digits in cell J7

I hope somebody can help.
Click to expand...

It may be possible to do this using Windows API calls to monitor the
keyboard, but it'd be extremely intrusive and fragile.

It's also possible to do this for the numeral keys on the standard
typewriter keypad, i.e., the row of keys above the QWERTY row of keys. It
requires an system like so.


Option Explicit

Private ds3 As String

Sub foo()
ds3 = ""
Application.OnKey "-", "unfoo"
Application.OnKey "0", "dgt0"
Application.OnKey "1", "dgt1"
Application.OnKey "2", "dgt2"
Application.OnKey "3", "dgt3"
Application.OnKey "4", "dgt4"
Application.OnKey "5", "dgt5"
Application.OnKey "6", "dgt6"
Application.OnKey "7", "dgt7"
Application.OnKey "8", "dgt8"
Application.OnKey "9", "dgt9"
MsgBox "foo'ed"
End Sub

Sub unfoo()
If ds3 <> "" Then ActiveCell.Formula = "'" & ds3
Application.OnKey "0"
Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
Application.OnKey "5"
Application.OnKey "6"
Application.OnKey "7"
Application.OnKey "8"
Application.OnKey "9"
MsgBox "unfoo'ed"
End Sub

Sub dgt(n As Long)
If Len(ds3) >= 3 Then dgtflsh True 'just in case
ds3 = ds3 & Format(n)
dgtflsh (Len(ds3) >= 3)
End Sub

Sub dgtflsh(s As Boolean)
ActiveCell.Formula = "'" & ds3
If s Then
ds3 = ""
ActiveCell.Offset(1, 0).Activate
End If
End Sub

Sub dgt0(): dgt 0: End Sub

Sub dgt1(): dgt 1: End Sub

Sub dgt2(): dgt 2: End Sub

Sub dgt3(): dgt 3: End Sub

Sub dgt4(): dgt 4: End Sub

Sub dgt5(): dgt 5: End Sub

Sub dgt6(): dgt 6: End Sub

Sub dgt7(): dgt 7: End Sub

Sub dgt8(): dgt 8: End Sub

Sub dgt9(): dgt 9: End Sub


I don't believe OnKey supports the numeral keys in the number keypad.

It may be better & easier to train your users how to use the [Enter] key.
 
I you train your users to use the enter key, arrow key, or tab key
to complete the entry as suggested by Harlan then the part
about continuing in Cell C11 after entry in Cell J7 might be:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'. Paste this Worksheet
' event macro into the module. (only one Option Explicit at top)
If Target.address(0,0) = "J7" then range("C11").activate
end sub

Another way is to unprotect cells J7, C11 and other cells
that you can change, then protect the worksheet.
If there are no unprotected cells between J7 and C11
then you will be placed at C11 automatically.

I did not put a check in for 3 digits, because don't know if that
is for text or number as in leading zeros for 003 or
possibly value must be from 100 through 999


Harlan Grove said:
mikewild2000 > said:
I have a little VB code in a "worksheet_change" decalration.

I am trying to add code that will move the focus to C11 once the user
has inputted 3 digits in cell J7

I hope somebody can help.
Click to expand...

It may be possible to do this using Windows API calls to monitor the
keyboard, but it'd be extremely intrusive and fragile.

It's also possible to do this for the numeral keys on the standard
typewriter keypad, i.e., the row of keys above the QWERTY row of keys. It
requires an system like so.


Option Explicit

Private ds3 As String

Sub foo()
ds3 = ""
Application.OnKey "-", "unfoo"
Application.OnKey "0", "dgt0"
Application.OnKey "1", "dgt1"
Application.OnKey "2", "dgt2"
Application.OnKey "3", "dgt3"
Application.OnKey "4", "dgt4"
Application.OnKey "5", "dgt5"
Application.OnKey "6", "dgt6"
Application.OnKey "7", "dgt7"
Application.OnKey "8", "dgt8"
Application.OnKey "9", "dgt9"
MsgBox "foo'ed"
End Sub

Sub unfoo()
If ds3 <> "" Then ActiveCell.Formula = "'" & ds3
Application.OnKey "0"
Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
Application.OnKey "5"
Application.OnKey "6"
Application.OnKey "7"
Application.OnKey "8"
Application.OnKey "9"
MsgBox "unfoo'ed"
End Sub

Sub dgt(n As Long)
If Len(ds3) >= 3 Then dgtflsh True 'just in case
ds3 = ds3 & Format(n)
dgtflsh (Len(ds3) >= 3)
End Sub

Sub dgtflsh(s As Boolean)
ActiveCell.Formula = "'" & ds3
If s Then
ds3 = ""
ActiveCell.Offset(1, 0).Activate
End If
End Sub

Sub dgt0(): dgt 0: End Sub

Sub dgt1(): dgt 1: End Sub

Sub dgt2(): dgt 2: End Sub

Sub dgt3(): dgt 3: End Sub

Sub dgt4(): dgt 4: End Sub

Sub dgt5(): dgt 5: End Sub

Sub dgt6(): dgt 6: End Sub

Sub dgt7(): dgt 7: End Sub

Sub dgt8(): dgt 8: End Sub

Sub dgt9(): dgt 9: End Sub


I don't believe OnKey supports the numeral keys in the number keypad.

It may be better & easier to train your users how to use the [Enter] key.
Click to expand...
 
The code doesn't need to be complex. First test that its
a number, then count the digits.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$7" Then '[A]

If IsNumeric(Target.Value) Then '

If Len(Target.Text) = 3 Then '[C]

Range("C11").Select
Exit Sub

End If '[C]

End If '

MsgBox "Enter a three digit number please"

End If '[A]

End Sub



The letters in square brackets eg [A] aren't required,
but it helps check that the IF-END IF's match.
As a general rule, always close a loop before writing the
code eg
FOR blah

Next
If blah

End If
DO

Loop

do this regularly & you'll not have to remember after a
big block of code is written. There's nothing I hate more
than the compiler responding with "Block End Without End
If" when there's loads of code.
On the other hand, I also say that if there's lots of
code, then it probably hasn't been written properly
anyways ! <VBG>

Patrick Molloy
Microsoft Excel MVP
 
Harlans idea is complicated, without disrespect i like to keep code
simple.

Patricks idea is good, but it does not work.

It is always a numeric value of 3 numbers.

Another way would be to use a timer function, say after 5 secs the
focus moves to C11?

I have looked at the help but nothing is written on "timer"

The main problem is that, there is always a different user and some
aren't intelligent - they enter thier 3 digits and do not exit the
cell.
They need to exit the cell as i also use:
ThisWorkbook.Save
Which is the most importaint bit.
 
Patrick,

I liked your tip about using some comment to mark the start and end of
your IFs. Like most good ideas, it's so OBVIOUS once someone points it
out.

Also your tip about closing the IF before you write the code -- it's odd
but I do this with my FOR and FOR EACH loops, but I never have with my
IFs. I shall in future, though.

Thanks.
 
A clever idea.

Just to add a caution, however.

Works if you just start typing - however, if you go into edit mode by
hitting F2 or clicking in the formula bar, then it doesn't work since Onkey
doesn't work in Edit mode.

--
Regards,
Tom Ogilvy


Harlan Grove said:
mikewild2000 > said:
I have a little VB code in a "worksheet_change" decalration.

I am trying to add code that will move the focus to C11 once the user
has inputted 3 digits in cell J7

I hope somebody can help.
Click to expand...

It may be possible to do this using Windows API calls to monitor the
keyboard, but it'd be extremely intrusive and fragile.

It's also possible to do this for the numeral keys on the standard
typewriter keypad, i.e., the row of keys above the QWERTY row of keys. It
requires an system like so.


Option Explicit

Private ds3 As String

Sub foo()
ds3 = ""
Application.OnKey "-", "unfoo"
Application.OnKey "0", "dgt0"
Application.OnKey "1", "dgt1"
Application.OnKey "2", "dgt2"
Application.OnKey "3", "dgt3"
Application.OnKey "4", "dgt4"
Application.OnKey "5", "dgt5"
Application.OnKey "6", "dgt6"
Application.OnKey "7", "dgt7"
Application.OnKey "8", "dgt8"
Application.OnKey "9", "dgt9"
MsgBox "foo'ed"
End Sub

Sub unfoo()
If ds3 <> "" Then ActiveCell.Formula = "'" & ds3
Application.OnKey "0"
Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
Application.OnKey "5"
Application.OnKey "6"
Application.OnKey "7"
Application.OnKey "8"
Application.OnKey "9"
MsgBox "unfoo'ed"
End Sub

Sub dgt(n As Long)
If Len(ds3) >= 3 Then dgtflsh True 'just in case
ds3 = ds3 & Format(n)
dgtflsh (Len(ds3) >= 3)
End Sub

Sub dgtflsh(s As Boolean)
ActiveCell.Formula = "'" & ds3
If s Then
ds3 = ""
ActiveCell.Offset(1, 0).Activate
End If
End Sub

Sub dgt0(): dgt 0: End Sub

Sub dgt1(): dgt 1: End Sub

Sub dgt2(): dgt 2: End Sub

Sub dgt3(): dgt 3: End Sub

Sub dgt4(): dgt 4: End Sub

Sub dgt5(): dgt 5: End Sub

Sub dgt6(): dgt 6: End Sub

Sub dgt7(): dgt 7: End Sub

Sub dgt8(): dgt 8: End Sub

Sub dgt9(): dgt 9: End Sub


I don't believe OnKey supports the numeral keys in the number keypad.

It may be better & easier to train your users how to use the [Enter] key.
Click to expand...
 
mikewild2000 > said:
Harlans idea is complicated, without disrespect i like to keep code
simple.
Click to expand...
....

No offense taken. It is complex. There's no mechanism built into Excel's
object model that would allow you to trap individual keystrokes. All the
ways that could allow you to do so are complicated. So the question you need
to consider is do you accept complexity or give up trying to do this?
Another way would be to use a timer function, say after 5 secs the
focus moves to C11?
Click to expand...

I guarantee you'll have some users who would hate this.
I have looked at the help but nothing is written on "timer"
Application.OnTime

The main problem is that, there is always a different user and some
aren't intelligent - they enter thier 3 digits and do not exit the
cell.
They need to exit the cell as i also use:
ThisWorkbook.Save
Which is the most importaint bit.
Click to expand...

So your users would be entering the 3 digits just before the file is saved
(and closed?), so you want to catch the final digit entry, enter the 3
digits, then save to ensure the file is saved?

Would each user always enter the same 3-digit number? If so, you could put
their own individual 3-digit code into an environment variable, and access
it without user intervention from, say, a command button macro. This macro
could also save the file. It a near certainty your users would find it
easier to click a button than enter a 3-digit number.

Still, better to train your users to use the [Enter] key. Even if it seems a
forlorn hope, that'd be the most reliable solution.
 
Tom Ogilvy said:
Works if you just start typing - however, if you go into edit mode by
hitting F2 or clicking in the formula bar, then it doesn't work since Onkey
doesn't work in Edit mode.
Click to expand...
....

Definitely not robust. Also not easy to correct mistyped 3rd digits, though
I suppose I could have trapped the [Backspace] key an a few others.

Maybe worthwhile to check the 123 user forum. 123 still provides {GET} and
{LOOK} macro statements that trap single keystrokes (so it *can* be done by
some API call), but these two were very seldom used, and macros using them
were very complicated and very fragile.
 
PATRICK

You wrote this

The code doesn't need to be complex. First test that its
a number, then count the digits.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$7" Then '[A]

If IsNumeric(Target.Value) Then '

If Len(Target.Text) = 3 Then '[C]

Range("C11").Select
Exit Sub

End If '[C]

End If '

MsgBox "Enter a three digit number please"

End If '[A]

End Sub



QUESTION:
How do you select a group of cells
 
Back
Top