Auto Entry in a Cell

[englishtwit]

Hi

This sounds simple, but I am flummuxed.

I doing alot of fast analysis that requires me to enter Y or N in a
cell, then move down one line.

ie
I press Y, then the down arrow to move to the cell underneath.

Is there a way of setting or formating the cell so that you only have
to press Y (or N) and the cursor will move to the next line?

I've tried setting up a macro with a shortcut key, but Excel doesn't
record the down arrow, or the carraige return.

I am using Excel 2003.

Looking forward to the usual great responses!

ET

 

[Guest]

Go to Tools>Options

Select the Edit tab and then select the Move selection after enter to "Down">

HTH

Ian

 

[Guest]

Just realised that this doesn't really help you! I'll have another think.

Ian

 

[Dave Peterson]

Nope.

You could change that "move selection after enter" setting, but you'd still have
to hit enter.

The only way I know around this is to create a userform that only accepts y/n
and then drops down one row.

Modifid from a previous post:

Another alternative is to create a tiny userform that just looks for a y,Y,n,N.

Put a single textbox on it (use the X button to close the userform).

Put this code in a General module:

Option Explicit
Sub testme01()
'start in column A of the row with the activecell
Cells(ActiveCell.Row, "A").Activate
UserForm1.Show
End Sub

Add this code to the userform module:

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

Select Case KeyAscii
Case Asc("y"), Asc("Y"), Asc("n"), Asc("N")
With ActiveCell
.Value = Chr(KeyAscii)
.Offset(1, 0).Activate
End With
End Select
KeyAscii = 0
TextBox1.Value = ""

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some getstarted instructions for userforms at:
http://contextures.com/xlUserForm01.html

 

[englishtwit]

Dave

many thanks for this - I've not got it working yet, but I understand
the principal and will have a look at getting this working for. (I
have decided to make a 'Y' and 'N' command button on a left and right
click... keep me busy for a bit!)

Thanks!

Dom