How to move to next cell by entering data-not using Enter key.

[johnexcel]

I have posted this as a new user but hought I may get a better repons
here.I am using ms excel to enter data, 1 for Yes and 2 for No, for 30
items/questions listed successively in column A. The data is the
tallied on another excel page. However, I would like to have the entr
of 1 or 2 provide the jump to the next cell down ( each time1 or 2 i
entered) rather than have to always press enter key to go to the nex
cell. Any ideas how to proceed

 

[Earl Kiosterud]

John.

No direct way. When anything is entered into a cell, Excel goes into Edit
mode, and won't come out of it until Enter (or one of it's equivalents) is
done. No events fire, so a macro solution isn't possible.

Some possibilities:

1)
Use a modeless UserForm that puts the 1's and 2's into the sheet as they're
typed. This approach requires the making of a UserForm, and the macro
coding to put the data in the cells, and indicate which row the user is on,
such via the cell selection. The user can select a different cell, but must
remember to click back into the UserForm box for this to work. Here's a
barebones routine:

Private Sub TextBox1_Change()
Static TextBox1Busy As Boolean
ActiveCell = Right(TextBox1.Value, 1)

If TextBox1Busy Then Exit Sub ' retrigger trap
TextBox1Busy = True
TextBox1.Value = Right(TextBox1.Value, 1) ' trash prior digit
TextBox1Busy = False
ActiveCell.Offset(1, 0).Select ' move down
End Sub

2)
Use the OnKey method on the 1 and 2 keys. The associated macro routines
would put the 1 or 2 in the selected cell, and move the selection down. It
could probably be done by a worksheet_selection event whenever the user is
determined to be in column A. When any other cells (or any other sheets)
are selected, it could reset the OnKey's for normal use of the 1 and 2 keys.
There's a potential trap if the user switches to a different workbook. I
haven't thought this all the way through, but it has a chance.

3)
Use two macros, fired by keys like Ctrl-i and Ctrl-o to put the 1's and 2's
in the cell and move the selection down. Unfortunately, you can't use
Ctrl-1 and Ctrl-2, which would be the most straightforward. This is
probably the least trouble-prone solution.

Sub Put1()
ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select ' move down
End Sub

Sub Put2()
ActiveCell.Value = 2
ActiveCell.Offset(1, 0).Select ' move down
End Sub

 

[johnexcel]

Earl

I greatly appreciate your reply. I was hoping for a direct solution an
now I understand why this is not possible. As I am not a programmer,
will start to review the excel help files with the examples yo
provided here as a "base". I will let you know how I proceed and than
you.

Joh

 

[Earl Kiosterud]

John,

For solution 3, you need only to copy/paste the two procedures from my reply
into a general module in the VBE. For instructions on that, look at David
McRitchie's site http://www.mvps.org/dmcritchie/excel/getstarted.htm.

Then you need to assign shortcuts to the macros:

Tools, Macro - Macros. Select a macro. Click Options. Assign the
shortcut.