How can I advance to the next field automatically?

[Guest]

I have a template that I have created that is to be used for data entry. I
have a validation on each cell that allows no more than one character. What
I would like happen is once that character is entered, it automatically goes
to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys.

For example, for the last name, there is a field for each alpha of the last
name and right now I have to enter each alpa and then tab to the next field
and type in the second letter of the last name and then so on and so on. I
just want to be able to type continuously and have each alpha placed in the
corresponding column.

I know there has to be a way. Any help would be appreciated.

 

[Guest]

I'll be watching this one - it's related to a feature I'd like to use also.
But the problem is that I don't know of a way of trapping a character as it's
typed into a cell. The Worksheet_Change() event would seem the place to look
for this type of thing, but the _Change() event doesn't trigger until you hit
that [Tab], [Enter], or Arrow key or click in another cell - i.e. not until
you (also) change the selected cell.

 

[Guest]

I know, it is driving me crazy. I was hoping it would be in the validation
option when I defined the field with one character. Too bad there isn't a
check box that states move to next unlocked cell. I guess that would be too
simple.

If you find anything out, please let me know.

I'll be watching this one - it's related to a feature I'd like to use also.
But the problem is that I don't know of a way of trapping a character as it's
typed into a cell. The Worksheet_Change() event would seem the place to look
for this type of thing, but the _Change() event doesn't trigger until you hit
that [Tab], [Enter], or Arrow key or click in another cell - i.e. not until
you (also) change the selected cell.

I have a template that I have created that is to be used for data entry. I
have a validation on each cell that allows no more than one character. What
I would like happen is once that character is entered, it automatically goes
to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys.

For example, for the last name, there is a field for each alpha of the last
name and right now I have to enter each alpa and then tab to the next field
and type in the second letter of the last name and then so on and so on. I
just want to be able to type continuously and have each alpha placed in the
corresponding column.

I know there has to be a way. Any help would be appreciated.

 

[Guest]

Hi,

This is not a very elegant way of doing it and I await nicer solutions:

In the worksheet (or workbook) code sheet paste the following.

Sub Worksheet_Activate()
Application.OnKey "A", "Move_A"
Application.OnKey "a", "Move_a"
Application.OnKey "B", "Move_B"
'…. For every key!!!!….
End Sub

Sub Worksheet_Deactivate()
Application.OnKey "A"
End Sub

Thes are in a seperate module (can't get it to work in the worksheet code)
paste the following:

Sub MoveR(c as string)
Application.EnableEvents = False
ActiveCell = c
ActiveCell.Offset(0, 1).Activate
Application.EnableEvents = True
End Sub

Sub Move_A()
MoveR("A")
End Sub
Sub Move_a()
MoveR("A")
End Sub
Sub Move_B()
MoveR("A")
End Sub

There needs to be some tidying up and careful reviewing of the events you
may also need to check the target address to ensure you only deal with the
cells that require special treatment.

I watch with interest on improvements on getting the keystroke or
dynamically calling a macro with onkey (not worked for me as in

application.onkey "mover(""A"")"

 

[Joerg]

Would it be a solution to have a separate input cell, where you would input
the name normally (typing continuously, then ENTER)?
The one-letter cells, where you want to end up each character of the entered
name would contain formulas to extract the characters. Data validation is
not required anymore.

Example:
Input cell is A1. Cells B1, C2.... contain the fomulas =MID(A1,1,1) ,
=MID(A1,2,1) etc.

Cheers,

Joerg Mochikun

 

[Guest]

It looks to me (without testing) that Martin Fishlock's solution could work
for you. I hadn't thought of using the OnKey. As he said, it's not elegant
but could probably work. Only problem I can think of right away is when you
get to the end of the row or if there are blank cells to be left between
"fields". But you could actually use his suggestion combined with
Application.Intersect to determine which column you are in and adjust the
forced moves to next cell to skip columns or bounce down to the next row.

My own initial thoughts were more like Joerg (and Martin elaborated on) -
either have a cell or form to type the entire entry into and then have a
little VBA code to break the entries out into the individual cells on the
worksheet. I didn't suggest the cell method because I figured you didn't
want to clutter up your form with added cells that you need to deal with.
But it would be one way to go.

I know, it is driving me crazy. I was hoping it would be in the validation
option when I defined the field with one character. Too bad there isn't a
check box that states move to next unlocked cell. I guess that would be too
simple.

If you find anything out, please let me know.

I'll be watching this one - it's related to a feature I'd like to use also.
But the problem is that I don't know of a way of trapping a character as it's
typed into a cell. The Worksheet_Change() event would seem the place to look
for this type of thing, but the _Change() event doesn't trigger until you hit
that [Tab], [Enter], or Arrow key or click in another cell - i.e. not until
you (also) change the selected cell.

I have a template that I have created that is to be used for data entry. I
have a validation on each cell that allows no more than one character. What
I would like happen is once that character is entered, it automatically goes
to the next cell w/o having to use the Enter/Tab/Arrow Rgt keys.

For example, for the last name, there is a field for each alpha of the last
name and right now I have to enter each alpa and then tab to the next field
and type in the second letter of the last name and then so on and so on. I
just want to be able to type continuously and have each alpha placed in the
corresponding column.

I know there has to be a way. Any help would be appreciated.

 

[L. Howard Kittle]

I would suggest a Worksheet Change Event macro solution. Where you type the
last name and hit Enter and the macro takes the name and puts the letters in
separate cells. Type in the first name and hit Enter and same thing but to
a different set of single cells.

I use this method quite successfully in a puzzle solving worksheet I have.
If you want, you could send me a sample workbook with details of what you
want and I will give it a shot.

Regards,
Howard
(e-mail address removed)