controlling cursor movement

[billa41]

I am trying to figure out how to control cursor movement to enter figures in three columns. I want to set the cursor at a starting point, enter a number hit enter, have the cursor move 1 cell to the right, enter a second number, hit enter, have the cursor move 1 cell to the right, enter a third number, hit enter and have the cursor drop 1 row and move 2 spaces to the left so that it is directly under the first number I entered. I want to continue this movement patter until all my data is entered. there are hundreds of lines of data to enter and having the cursor movement occur upon hitting the enter key, rather than using arrows will save tons of keystrokes and time.

Thanks for any help

Billa41

 

[jproffer]

Lock all cells in all columns except the ones you want to use. Protect the sheet and uncheck the box that says "select locked cells".

Then in options/edit tab/ "move selection after enter" change it to "right".

Quick testing showed that if you unlock the ENTIRE column(by selecting the columns and unlocking), it won't do what you want, but if you leave row 65536 locked, it will.

 

[Tazguy37]

Welcome to MrExcel Board!

Something like this should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    If Target.Column = 4 Or Target.Column = 5 Then      'Column D & E
        ActiveCell.Offset(-1, 1).Select
    ElseIf Target.Column = 6 Then                       'Column F
        ActiveCell.Offset(, -2).Select
    End If
    
    Application.EnableEvents = True
End Sub

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Double-click the sheet where you need this to work.
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

Hope that helps!

 

[erik.van.geit]

to continue on jproffer's response...

Then in options/edit tab/ "move selection after enter" change it to "right".

If you don't want to protect the sheet, just select the range where you're going to fill in. Then enter your data. When the right side of the selection is reached the activecell will jump again to the right.

One little problem: when you make a mistake, you'll have to select again. You can make a button (freezepanes on top to let your button stay on screen) with a macro to select a range ...

here three options:
ActiveCell.Resize(20, 3).Select
Cells(ActiveCell.Row, 3).Resize(20, 3).Select
Cells(65536, 3).End(xlUp).Offset(1, 0).Resize(20, 3).Select

kind regards,
Erik

 

[parry]

Hi billa41, welcome to the board.

I see youve been given some options to work on the Enter key, but I thought you should be aware of how Excel's movement works in case you didnt know.

You already know that if you press Enter the next cell selected will be underneath the curent one (presuming the default Down option is in your settings). However, if you use the Tab key the cell selected is to the right and the next time you press Enter a carriage return affect will occur returning you to the cell underneath the first cell you pressed tab on.

For example, lets say you have data in Columns C-E. This is what will happen...

1. C1 you enter something and press tab. The cell selected is D1
2. D1 you enter something and press tab. The cell selected is E1
3. E1 you enter something and press Enter. The cell selected is C2

So think of tab moving you to another field to do with the same record and then Enter is a carriage return. If you like this also see the pre-built forms option under Data|Form. It requries tab between fields and enter for a new record.

PS: Dont be afraid to post again and ask more questions if a reply isnt clear to you or you still havent got the desired answer. As you can see theres a lot of willing people here. :wink: