"Auto-return"

J

Jaytee

Question 1: If I'm entering rows of data, can I set it up at a certain column
to return to the start of the next row?
So if I have 4 columns of data, I input in A2, hit enter to get to B2, then
input and hit enter to get to c2, input and enter to get to d2, input and
enter to get to b3.
 
P

Paul B

Jaytee, here is some code that will do it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
Target.Offset(1, -3).Select
End Sub

To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

To change the security settings go to tools, macro, security, security level
and set it to medium


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
R

Ragdyer

Some things you can try:

If you start entering data in B2, hit <Tab> to move right each time.
When you come to the end of the set (E2), hit <Enter>, and the focus moves
to C2 automatically (cell under the first cell you used <Tab> on).

OR

You could click and drag and create a selected range, where the first cell
of data entry has the focus (colored white).
Now, hold down the <Enter> key, and you'll see that the focus moves down and
to the right, but *remains* within the selection.
Hold <Tab>, and the focus moves right and then down, remaining within the
selection.

OR

Select a cell, hold <Ctrl>, and click in other non-adjacent cells, making a
non-contiguous range.
Hitting <Enter> or <Tab> will now move the focus to each cell, in the
*order* that you selected them.

In fact, you can name and save these selections to be used for filling
forms.

Check out this old post about these "named ranges".

http://tinyurl.com/39vzv

Another option is to unlock the cells that you wish to accept data entry,
and then protect the sheet.

When you hit <Tab> on a protected sheet, the focus will move from
unprotected cell to unprotected cell.
 
N

Norman Jones

Hi Jaytree,

Jaytee said:
Question 1: If I'm entering rows of data, can I set it up at a certain
column
to return to the start of the next row?
So if I have 4 columns of data, I input in A2, hit enter to get to B2,
then
input and hit enter to get to c2, input and enter to get to d2, input and
enter to get to b3.
Click to expand...

If you want a VBA solution, try:

'===================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngInput As Range
Dim i As Long

Set RngInput = Range("A1:D50") '<<========= CHANGE
i = RngInput.Columns.Count

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, RngInput) Is Nothing Then
If Target.Column = RngInput.Columns(i).Column Then
Target.Offset(1, 1 - i).Select
Else
Target.Offset(0, 1).Select
End If
End If
End Sub
'===================>>

Change Range("A1:D50") to match the required input range.

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

************************************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
************************************************************

If you are not familiar with macros you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top