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


--
johnexcel
------------------------------------------------------------------------
johnexcel's Profile: http://www.excelforum.com/member.php...o&userid=26600
View this thread: http://www.excelforum.com/showthread...hreadid=398818

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

--
Earl Kiosterud
www.smokeylake.com


"johnexcel" wrote
in message ...

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


--
johnexcel
------------------------------------------------------------------------
johnexcel's Profile:
http://www.excelforum.com/member.php...o&userid=26600
View this thread: http://www.excelforum.com/showthread...hreadid=398818

Earl

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

John


--
johnexcel
------------------------------------------------------------------------
johnexcel's Profile: http://www.excelforum.com/member.php...o&userid=26600
View this thread: http://www.excelforum.com/showthread...hreadid=398818

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.
--
Earl Kiosterud
www.smokeylake.com

"johnexcel" wrote
in message ...

Earl

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

John


--
johnexcel
------------------------------------------------------------------------
johnexcel's Profile:
http://www.excelforum.com/member.php...o&userid=26600
View this thread: http://www.excelforum.com/showthread...hreadid=398818