Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default Single Digit Entry

I have three separate cells defined as hundreds, tens and ones. I have
data validation set to only allow values between 0 and 9. Is there any
way to go to the next cell after a digit has been entered, without
pressing ENTER, TAB or Right Arrow? (i.e. Press "7" on numeric keypad
on tens cell, automatically go to ones field.)

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default Single Digit Entry

Hi Paul
not really possible. Harlan Grove posted some time ago a procedure
using API calls, etc. Problem is: Macros are not executed while you're
in edit mode. So you have to hit ENTER or TAB

--
Regards
Frank Kabel
Frankfurt, Germany


Paul S Panoff wrote:
I have three separate cells defined as hundreds, tens and ones. I

have
data validation set to only allow values between 0 and 9. Is there
any way to go to the next cell after a digit has been entered,

without
pressing ENTER, TAB or Right Arrow? (i.e. Press "7" on numeric

keypad
on tens cell, automatically go to ones field.)


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22,906
Default Single Digit Entry

Paul

When you are typing data in a cell you are in <ENTER mode.

Excel has no way of knowing what is in a cell until you leave that cell by
Enter, TAB or arrowing out.

In short.....NO.

Gord Dibben Excel MVP


On Tue, 20 Apr 2004 14:48:38 -0400, Paul S Panoff
wrote:

I have three separate cells defined as hundreds, tens and ones. I have
data validation set to only allow values between 0 and 9. Is there any
way to go to the next cell after a digit has been entered, without
pressing ENTER, TAB or Right Arrow? (i.e. Press "7" on numeric keypad
on tens cell, automatically go to ones field.)


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 126
Default Single Digit Entry

There is a somewhat cumbersome way of doing this. You have to define a
OnKey procedure for each of the numbers. For example, the following
will cause the active cell to change as soon as someone types the number
7. Run the testOnKey procedure to enable the automatic change to the
active cell. The resetOnKey will return the functionality of the 7 key
to its default status. The OnKeySub does the actual work.

Sub testOnKey()
Application.OnKey "7", "OnKeySub"
End Sub
Sub resetOnKey()
Application.OnKey "7"
End Sub
Sub OnKeySub()
ActiveCell.Value = 7
ActiveCell.Offset(0, 1).Select
End Sub

At the very least you will have to set the OnKey procedure for each of
the numeric keys. Combine that with validation for non-numeric values.

Alternatively, you will have to define the OnKey procedure for *every*
keystroke. And, you can skip the validation stuff.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Business solutions leveraging technology
Microsoft Most Valuable Professional (MVP) 2000-2004

In article ,
says...
I have three separate cells defined as hundreds, tens and ones. I have
data validation set to only allow values between 0 and 9. Is there any
way to go to the next cell after a digit has been entered, without
pressing ENTER, TAB or Right Arrow? (i.e. Press "7" on numeric keypad
on tens cell, automatically go to ones field.)

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22,906
Default Single Digit Entry

Tushar

I must learn to never say "never"<g

Gord

On Tue, 20 Apr 2004 16:52:30 -0400, Tushar Mehta
<tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote:

There is a somewhat cumbersome way of doing this. You have to define a
OnKey procedure for each of the numbers. For example, the following
will cause the active cell to change as soon as someone types the number
7. Run the testOnKey procedure to enable the automatic change to the
active cell. The resetOnKey will return the functionality of the 7 key
to its default status. The OnKeySub does the actual work.

Sub testOnKey()
Application.OnKey "7", "OnKeySub"
End Sub
Sub resetOnKey()
Application.OnKey "7"
End Sub
Sub OnKeySub()
ActiveCell.Value = 7
ActiveCell.Offset(0, 1).Select
End Sub

At the very least you will have to set the OnKey procedure for each of
the numeric keys. Combine that with validation for non-numeric values.

Alternatively, you will have to define the OnKey procedure for *every*
keystroke. And, you can skip the validation stuff.




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Single Digit Entry

Another way is to build a tiny userform with just a textbox on it.

Add this code to the userform module:

Option Explicit
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
Case 48 To 57 'Numbers 0-9
With ActiveCell
.Value = Chr(KeyAscii)
If .Column = 3 Then
.Offset(1, -2).Activate
Else
.Offset(0, 1).Activate
End If
End With
End Select
KeyAscii = 0
TextBox1.Value = ""

End Sub

Then add this to a general module to show the form:
Option Explicit
Sub testme01()
Cells(ActiveCell.Row, 1).Activate
UserForm1.Show
End Sub


I always start in column A and use A:C.



Paul S Panoff wrote:

I have three separate cells defined as hundreds, tens and ones. I have data
validation set to only allow values between 0 and 9. Is there any way to go
to the next cell after a digit has been entered, without pressing ENTER, TAB
or Right Arrow? (i.e. Press "7" on numeric keypad on tens cell, automatically
go to ones field.)


--

Dave Peterson

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,071
Default Single Digit Entry

Hi Gord,

In article , Gord Dibben
<gorddibbATshawDOTca says...
Tushar

I must learn to never say "never"<g


Maybe, but the solution I proposed is sufficiently clumsy that it might
be better to say "never." <g

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , Gord Dibben
<gorddibbATshawDOTca says...
Tushar

I must learn to never say "never"<g

Gord

On Tue, 20 Apr 2004 16:52:30 -0400, Tushar Mehta
<tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote:

There is a somewhat cumbersome way of doing this. You have to define a
OnKey procedure for each of the numbers. For example, the following
will cause the active cell to change as soon as someone types the number
7. Run the testOnKey procedure to enable the automatic change to the
active cell. The resetOnKey will return the functionality of the 7 key
to its default status. The OnKeySub does the actual work.

Sub testOnKey()
Application.OnKey "7", "OnKeySub"
End Sub
Sub resetOnKey()
Application.OnKey "7"
End Sub
Sub OnKeySub()
ActiveCell.Value = 7
ActiveCell.Offset(0, 1).Select
End Sub

At the very least you will have to set the OnKey procedure for each of
the numeric keys. Combine that with validation for non-numeric values.

Alternatively, you will have to define the OnKey procedure for *every*
keystroke. And, you can skip the validation stuff.



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Single Digit Entry

Hi, how should i understand "userform module" and "general module". Please help me. Allan
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
HOW DO I CONVERT A NUMBER'S MOST SIGNIFICANT DIGIT TO A SINGLE DI. maxq777 Excel Worksheet Functions 4 March 23rd 08 11:40 PM
16 digit number entry changes last digit to 0 Donald T Excel Discussion (Misc queries) 1 February 28th 08 12:03 AM
Can only put a single digit/letter in worksheet 2007 Igor Quasimodo New Users to Excel 1 February 6th 07 08:17 PM
how to convert single digit to month name cygnus Excel Worksheet Functions 4 April 27th 06 05:16 PM
Color a single digit in a mult-digit number cell Phyllis Excel Discussion (Misc queries) 6 November 17th 05 01:46 AM


All times are GMT +1. The time now is 01:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"