Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single Digit Entry
Hi, how should i understand "userform module" and "general module". Please help me. Allan
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I CONVERT A NUMBER'S MOST SIGNIFICANT DIGIT TO A SINGLE DI. | Excel Worksheet Functions | |||
16 digit number entry changes last digit to 0 | Excel Discussion (Misc queries) | |||
Can only put a single digit/letter in worksheet 2007 | New Users to Excel | |||
how to convert single digit to month name | Excel Worksheet Functions | |||
Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) |