Enter key goes to Column 'A'

[Elyod]

I have some users who enter record data in a table. They want, when
they get to the end of a record, for the ENTER key to make the active
cell the first cell in a new record, or go to the next row in Column
'A'. Any ideas and I have suggested that they use the DATA/FORM, but
they want to work in the table?

This seems too easy and I have used EXCEL for a long, long time and
never had this question.

 

[JohnI]

Elyod,

Put the following code into the Sheet event module.
It should do what you're after.
=========================================
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column > 5 Then ' Return to 1st Column ("A") after 5th Column
filled.
Range("A" & Target.Row + 1).Select
End If

Application.EnableEvents = True

End Sub
===============================================

regards,

JohnI

 

[Elyod]

I will give them a try!

 

[J.E. McGimpsey]

Hmm...

That's the way XL (at least v.X) normally behaves - if preferences
are set for return to move down, if Return is pressed with
contiguous cells to the left, or when below a list of contiguous
columns, the cursor is placed on the next row in the first column of
the list.

 

[Earl Kiosterud]

Elyod,

Here are a couple of things you may wish to look at.

This can be done without code, but does require a bit of discipline on the
part of the user. If the user uses Tab to move across, then Enter in the
last column, it will drop down and move back to the left where the tabbing
started. To back up (move left), use Shift-Tab. If he uses the mouse or
the direction (up, down, etc) keys, it stops working for that row.

You may wish to use Data-Form instead. If your list is a standard table
(Column headings, and one row/record), and has no other stuff immediately
next to it, you need only select any single cell in the table (or you can
select the entire table), and then use Data - Form. It's a
one-record-at-a-time form. There will be named boxes for each field (from
your column headings). And there are navigation tools, and a search
facility. It takes a little getting used to, but is quite nice.

Earl Kiosterud

mvpearl omitthisword at verizon period net

 

[Martha]

What you need is an event macro. Specifically, a macro that will
execute whenever an entry change is made in a cell in a specific column.

No, he doesn't. Going back to the beginning of the row is default,
built-in Enter key behavior, *provided* you use Tab and Shift-Tab to
move between columns. Absolutely no code needed. Just train users not
to use the mouse or the arrow keys. (Or rather, teach them what the
arrow keys/mouse do: they reset the beginning of the row to the
current column. Sometimes, that's exactly what you want.)