ENTER button

[Guest]

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]

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
(e-mail address removed)

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]

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]

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]

<<"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]

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]

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]

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]

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?