ENTER button

RAWLEY · Jun 7, 2004

Isn't there a way to make it so that if you're entering data and tabbing over several columns as you go, that after you hit ENTER the box moves down one row and to the first column that you began entering data into

Thanks!

Don Guillett · Jun 7, 2004

Here is one I use that goes back to the original column and one row down. I
use the right arrow key.
right click sheet tab>view code>insert this>modify to suit.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row > 5 And Target.Column = 8 Then ActiveCell.Offset(1, -6).Select
End Sub
--
Don Guillett
SalesAid Software
[email protected]

RAWLEY said:
Isn't there a way to make it so that if you're entering data and tabbing

over several columns as you go, that after you hit ENTER the box moves down
one row and to the first column that you began entering data into?

RagDyer · Jun 7, 2004

You really *don't* have to make *anything*, because that's how it *is*!

Click in a cell and enter data, and then <Tab> to the next cell to enter
data.
Keep on entering and tabbing as necessary.
Then hit <Enter>, and the focus *automatically* moves to the next row,
directly under the cell where you started your first entry (wherever that
might have been).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Isn't there a way to make it so that if you're entering data and tabbing
over several columns as you go, that after you hit ENTER the box moves down
one row and to the first column that you began entering data into?

Thanks!

David McRitchie · Jun 8, 2004

Hi Rawley,
You can use an Event macro such as the following to make
things easier.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("b2").Value <> "" Then Exit Sub 'escape clause
If Target.Column < 6 Then Exit Sub 'okay until column F or later
On Error Resume Next 'MUST reenable events...
Application.EnableEvents = False
ActiveCell.Offset(1, 2 - Target.Column).Select 'return to column B instead of A of next row
Application.EnableEvents = True
End Sub

More information on Event macros and for the above macro in
Worksheet_SelectionChange (#ws_sc)
Example: Worksheet_SelectionChange to prevent entry past a column
http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc
be sure to read the top of the article as well.

To install this event macro, right click on sheetname then view code, and
insert your code. So if you have a lot of these to do you can actually
change your Enter key to go to the right instead of Down. Easy enough
to change in your tools, options, Edit. And this just reminded to
put mine back to "Down" since I was doing this yesterday.

RagDyer, with default "Down" the cursor normall goes straight
down after entry, not to first column of next row.

RagDyer · Jun 8, 2004

<<"RagDyer, with default "Down" the cursor normall goes straight
down after entry, not to first column of next row.">>

If you'll re-read my post David, you'll see that I stated, just as the OP
described his use of <Tab>, when you tab after data entry, for however many
entries, and *then* you hit <Enter>, the cursor *will* automatically go to
the *following* row, right *under* where you *started* with your first
<Tab>!

In fact, for this to occur, the default cursor movement *must* be set to
down.

If it's set to default "Up", then the focus will automatically shift to the
starting column, one row above the original first data entry cell.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi Rawley,
You can use an Event macro such as the following to make
things easier.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("b2").Value <> "" Then Exit Sub 'escape clause
If Target.Column < 6 Then Exit Sub 'okay until column F or later
On Error Resume Next 'MUST reenable events...
Application.EnableEvents = False
ActiveCell.Offset(1, 2 - Target.Column).Select 'return to column B
instead of A of next row
Application.EnableEvents = True
End Sub

More information on Event macros and for the above macro in
Worksheet_SelectionChange (#ws_sc)
Example: Worksheet_SelectionChange to prevent entry past a column
http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc
be sure to read the top of the article as well.

To install this event macro, right click on sheetname then view code, and
insert your code. So if you have a lot of these to do you can actually
change your Enter key to go to the right instead of Down. Easy enough
to change in your tools, options, Edit. And this just reminded to
put mine back to "Down" since I was doing this yesterday.

RagDyer, with default "Down" the cursor normall goes straight
down after entry, not to first column of next row.

David McRitchie · Jun 8, 2004

So that's how that works. I always wondered how it knew to
return to column B instead of column A. Thanks for not making
me reread -- I'd probably have skipped right over that distinction
again.

RagDyer said:
If you'll re-read my post David, you'll see that I stated, just as the OP
described his use of <Tab>, when you tab after data entry, for however many
entries, and *then* you hit <Enter>, the cursor *will* automatically go to
the *following* row, right *under* where you *started* with your first
<Tab>!

In fact, for this to occur, the default cursor movement *must* be set to
down.

If it's set to default "Up", then the focus will automatically shift to the
starting column, one row above the original first data entry cell.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi Rawley,
You can use an Event macro such as the following to make
things easier.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("b2").Value <> "" Then Exit Sub 'escape clause
If Target.Column < 6 Then Exit Sub 'okay until column F or later
On Error Resume Next 'MUST reenable events...
Application.EnableEvents = False
ActiveCell.Offset(1, 2 - Target.Column).Select 'return to column B
instead of A of next row
Application.EnableEvents = True
End Sub

More information on Event macros and for the above macro in
Worksheet_SelectionChange (#ws_sc)
Example: Worksheet_SelectionChange to prevent entry past a column
http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc
be sure to read the top of the article as well.

To install this event macro, right click on sheetname then view code, and
insert your code. So if you have a lot of these to do you can actually
change your Enter key to go to the right instead of Down. Easy enough
to change in your tools, options, Edit. And this just reminded to
put mine back to "Down" since I was doing this yesterday.

RagDyer, with default "Down" the cursor normall goes straight
down after entry, not to first column of next row.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

RagDyer said:

You really *don't* have to make *anything*, because that's how it *is*!

Click in a cell and enter data, and then <Tab> to the next cell to enter
data.
Keep on entering and tabbing as necessary.
Then hit <Enter>, and the focus *automatically* moves to the next row,
directly under the cell where you started your first entry (wherever that
might have been).

Isn't there a way to make it so that if you're entering data and tabbing
over several columns as you go, that after you hit ENTER the box moves down
one row and to the first column that you began entering data into?

Thanks!

Click to expand...

RagDyer · Aug 27, 2004

Do I understand you to say that when you hit your <Tab> key, the selection
(focus) moves *DOWN*?

So there is no distinction between the <Enter> key and the <Tab> key?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Greets, RagDyer... do you happen to know why a worksheet would STOP
behaving this way? My sheet used to do as you describe below but now it is
only going to the cell below no matter how I have the "Move selection after
Enter" (which is set to down now).

It is really slowing things down for me now. Any help is greatly
appreciated!

Cheers,
Daen

RagDyer · Aug 27, 2004

I'm afraid I can't duplicate this, so I can't offer a suggestion.

However>>What exactly do you mean by this?>>

<<"but somehow I've done something in my updating that has killed this nice
feature.">>

What updating ???
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Not quite. The problem lies in that though I can tab through the fields
as normal (to the right when tab is hit), when Enter is pressed the focus
goes to the cell below rather than the first cell of the next row in the
first column. I did start data entry with that column to be sure and it
still
went to the cell below.

It used to work as a carriage return (or at least would jump to the next
row on Enter), but somehow I've done something in my updating that has
killed
this nice feature.

Thank you for the quick reply, I hope that was a bit clearer. I'm sorry
I didn't explain better the first time.

Cheers!

RagDyer · Aug 30, 2004

You never mentioned "protection" before ... did you?

"Tab" doesn't work on a protected sheet!

That is, unless you *unlock* the range of cells that you wish to be able to
accept data input.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Just stating that it used to work and even though all I have done is
"protect" the sheet it no longer does this for my users. What if you turn
the
"Move Selection after Enter" off?