Excel - Enter one digit then move to next cell

[HezMac]

Hi

I'm putting together a spreadsheet to keep track of golf scores.

I'm wondering if there's a way to enter in only one digit (score for one hole) and then move directly on to the next cell (score for the next hole) without having to tab or click in the mouse in it.

Thanks for any advice.

 

[SkipVought]

Hi,

No. But you could enter all the scores in one cell and then on the Worksheet_Change event, parse the digits.

BTW, What happens if Duffer Dan slices into the rough off the par 5, dumps it twice in the pond (tree, richoche ), can't get it out of the trap, overshoots the green, ......

and ends up with a 15??? ;-)


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[DrBowes]

You could build a USerform and do this if you feel inclined.

 

[SkipVought]

BTW,

Using the Worksheet_Change event can be tricky when a change causes another change. You must turn OFF EnableEvents while the code executes and then turn EnableEvents back on.

Private Sub Worksheet_Change(ByVal Target As Range)
   With Target
      If .Row = 1 Then Exit Sub     'heading row
      If .Count > 1 Then Exit Sub   'possible paste
      Application.EnableEvents = False
      For i = 1 To Len(.Value)
         .Offset(0, i).Value = Mid(.Value, i, 1)
      Next
   End With
   Application.EnableEvents = True
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Zathras]

If you don't mind having to press the enter key after each number, you can use Tools/Options/Edit and check the "Move selection after Enter" option and set the direction to "Right" (No VBA required.)

 

[HezMac]

Dr Bowes

Any more information on User Form? I looked it up in Excel, and still not sure what it is, how to create...

Thanks

 

[SkipVought]

Open the VB Editor.

In the Project Explorer, [right click]/Insert & select UserForm.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[HezMac]

OK

But, still not getting the point of the user form, or how it would help in my situation.

 

[SkipVought]

Dr. Bowes' point was that by using a TextBox in a UserForm, you can detect keystrokes using the KeyUp, KeyDown & KeyPress events. BUT you will also have to tell your program what row & column you want the digits to be written to.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[HezMac]

Thanks for all your suggestions.

I thought there might be a *quick and simple* way to do this - no such luck!

Much appreciated!

 

[jasonwooten]

not really sure what you are trying to do with Excel, but, yes Excel will automatically go to the cell after you enter a number.(ex:a23 you enter 3, hit enter and it will go to A24) not sure if this is what you are talking about..


To do this ,
go to Tools, Options,Edit, move selection after enter. Choose what you want

 

[Zathras]

Another possibility is to use one cell for input and pick out the numbers with the MID function into the other cells.

You would have to enter the front nine and the back nine separately because of the limition on the number of digits Excel allows for a numeric entry. (Your scenario assumes that no one ever has more than 9 strokes on any one hole.)

So you could enter 535427224 in cell A10 and have formulas like these in B10 thru J10:
A10: 535427224
B10: =MID(A10,1,1)
C10: =MID(A10,2,1)
D10: =MID(A10,3,1)
etc.