make cell contain only 1 letter/number then tab automatically

  • Thread starter Thread starter Ron Lynn Jr
  • Start date Start date
R

Ron Lynn Jr

Good Day,
I have made a spreadsheet that contains cells that I only want to
contain one letter/number/character. So my question is when I enter that one
character is there a way to make Excel automatically tab/enter to the next
cell? Will I need to make a macro for each cell? Thanks for any assistance
that you can provide.
 
Excel has no way of knowing when you have finished entering/editing data in
a cell until you hit Enter, Tab or Arrow key to move out of the cell.

Macros don't work while in editing mode so that method is not an option.

So.................no, there is no way.


Gord Dibben MS Excel MVP

On Sun, 21 Sep 2008 09:56:01 -0700, Ron Lynn Jr <Ron Lynn
 
Well allrighty then, thanks anyway. I kinda had a feeling that there was no
way but thought it wouldn't hurt to ask. Thanks again, later
 
Saved from a previous post:

Without knowing what "the next cell" means...

I would turn
tools|options|edit tab|move selection after enter
to down or right or ...

Then turn number lock on and use the numeric keypad to type your digit

Hitting the enter key on the numeric keypad doesn't seem too bad to me.

Another alternative is to create a tiny userform that just looks for a digit:

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()
Cells(ActiveCell.Row, 1).Activate
UserForm1.Show
End Sub

Add this code to the userform module:

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

With ActiveCell
.Value = Chr(KeyAscii)
'A:E, then down a row
If ActiveCell.Column = 5 Then
ActiveCell.EntireRow.Cells(1).Offset(1, 0).Activate
Else
.Offset(0, 1).Activate
End If
End With
KeyAscii = 0
TextBox1.Value = ""

End Sub

This code goes from A:E then next row, column A.

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
 
Back
Top