assigning character length in a cell

[Guest]

I don't know if this is possible, but I'd like to basically tell a group of
cells how many characters it's allowed to have (one in this case), and after
I've entered the character, I'd like to automatically be moved to the next
cell without having to actually hit ENTER.

I need to enter about 600 1s and 2s, one number per cell, and as you can
imagine, hitting ENTER each time can become quite tedious.

Thanks in advance!

 

[VBA Noob]

Hi,

If you select the cells you want. Then go to data > validation. Select
length and change to equal to 1 that will restrict the text length to 1
character.


Next select the 600 cells click into the formula bar just below ariel
and this will allow you to tab throught all the cells

Only other way to avoid pressing enter would be a event macro

VBA Noob

 

[Guest]

Thanks for your answer. I tried it and it solved part of my problem, but I
should probably rephrase my questions b/c I just realized it wasn't very
clear. What I idealy would like is for my cursor or I guess the selection to
automatically move down to the next cell after I've finished typing in one so
that I don't have to hit Enter each time, which adds 600 extra keystrokes to
the other keystrokes I'm already making (I have to do this set 250 times). I
figured that if I could assign a character length to the cells, it would
eliminate the use of the Enter key, but that wasn't the case. I also tried
setting up a macro to no avail.

I basically want to type the numbers 1212122212221221221112 etc, each in its
own cell without hitting enter so the process goes much quicker.

I hope that makes more sense.

Audrey

 

[Guest]

tools option edit you can change the settings so that the cursor goes down
when you press enter or use the down arrow key intead of enter

 

[Gord Dibben]

Audrey

Until you hit Enter or Tab or an arrow key Excel has no way of knowing that you
are finished typing in that cell.

You must indicate in some way that you are ready to go to next cell.

Data Validation only shows a message after you have left the cell.

Event code still requires you to leave the cell before it triggers.


Gord Dibben MS Excel MVP

 

[Guest]

Audrey's inadequately answered question is an exact match to my own. I am
entering 22,440 values from hand-written surveys into a spreadsheet. I too
want to eliminate the need for pressing enter or tab after typing each
single-digit cell value.

I understand Gord Dibben's response about an event macro not being enabled
until the cell "knows" that data input is complete, but I have to believe
that there is another VBA method for automatically advancing to the next cell.

Eliminating those keystrokes would save a great deal of time and prevent
some physical strain.

Any thoughts that can re-open this discussion would be greatly appreciated.
Thank you.

JK

 

[Dave Peterson]

Modified from a previous post:

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

Then turn number lock on and use the numeric keypad to type the 1's and 2's.

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 1 or a 2.

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)

Select Case KeyAscii
Case 48-57 'Numbers 0-9
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
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