HELP - How do I allow only 1 char/numb per cell?

[Bisonhawk]

I would like to create a spreadsheet that simulates a report and would like
to be able to only enter 1 character or 1 number per cell. Is this possible
in Excel 2003 and how would I go about setting the worksheet to do this?????
Would really appreciate anyones help ASAP if at all possible. Thank you
very much in advance!!

 

[cm]

use validation -- on the menu, choose Data Validation. On the Settings tab,
choose Text Length and then select equal to 1 or between 0 and 1, to allow
for possibility of blank.

 

[Bisonhawk]

Thank you for the info. I really appreciate it. One more question: Is
there a way to make the cursor automatically go to the right one cell after
entering the 1 char/numb/blank so I don't have to press enter or tab after
each entry???? Thank you very much!!!!

 

[cm]

go to Tools / Option -- the Edit tab. There is a 'Move selection after enter'
where you make that choice.

 

[Jacob Skaria]

so I don't have to press enter or tab after each entry????

To answer your above question without pressing enter or tab..you
cannot...You can only adjust the 'Move selection after enter' as mentioned by
'cm'


If this post helps click Yes

 

[Bisonhawk]

I was afraid of that. I was just attempting to eliminate keystrokes.
Thank you to both of you!! CM & Jacob

 

[Gord Dibben]

Excel has no way of knowing when you have finished editing a cell until you
leave that cell.

Enter, Tab or arrow out of the cell is only way.

You could use VBA event code to jump to a specific cell when you leave by
any of the methods above.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Saved from a previous post:

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 one
character.

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
ActiveSheet.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:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

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