Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Carriage Return

Carriage Return

Carriage Return

This one has me a bit stumped...

Is there a quick and dirty way to emulate a carriage return from an old typewriter? I have about 10 columns of data and when an entry is put in the last column, I'd like the enter or return button to return the cursor to the next row down and 10 columns to the left.

I was thinking a custom function, but I can't seem to get it to work right.

Any thoughts?

Thanks for the help.

RE: Carriage Return

Can't you use  HOME and DOWN?


RE: Carriage Return

What you describe seems to be the default behaviour (I have 2000).  Tab moves right one column, return moves down one row, and left back to the first column.

There are settings related to this under Tools | Options | Edit.

I have "Move selection after Enter" checked, and "Direction" is Down.

RE: Carriage Return

Here's one method:

Highlight the cells into which you wish to enter data.  Then simply use the tab key after you've entered the data into a cell.  The tab key moves the focus to the cell on the immediate right.  When the focus is in the far right cell (of the highlighted cells), the tab key will automatically move the focus to the far left cell in the next row.

Note:  If your preferences are set to move right after enter (somewhat like MintJulep stated above except move right instead of down) you will get the same result when you highlight cells as you would by using the tab key as indicated above.

Another note:  If the focus is in the bottom right of the highlighted cells, the focus will shift to the top left using either of the two methods above.


RE: Carriage Return

Should have mentioned...  

If you make a mistake in entering data, you can shift-tab to go backwards to the previous cell(s) in the highlighted group.

RE: Carriage Return

Thanks to all for answering. To address some of the items mentioned:

- The data being entered is going to be a massive amount and extremely repetetive, so I was hoping to reduce the number of keystrokes the person entering the data will have. As I was making the spreadsheet, I was using the HOME/DOWN combo, but I was hoping to get it down to just one keystroke to put the cursor back to the beginning of the next row.

- The TOOLS/OPTIONS/ Move After Enter only moves the cursor in the direction specified by 1 cell. I have about 10 columns that I want to traverse, so that doesn't do all that I was looking for.

- I didn't think of highlighting the entire row. That's a good middle step.

I was wondering if there is a way to exectue a function or macro simply by the selection of a cell?

RE: Carriage Return

Not sure if you read InspEngr's suggestion to its ultimate embodiment; if you select ALL the columns in their entirety, <TAB> will step you through each cell in turn from top-left to bottom right.  This would be a single key solution without any programming.

Alternately, you can use the event "SelectionChange" to detect motion into a bordering cell and force the selection to move home and down


RE: Carriage Return

I misread his post. I was hilighting each row individually, not all at once. I'll give that a try.

The SelectionChange event is a new one to me. It sounds like what I was looking for.


RE: Carriage Return

In the VBA interface:
-Find the Project Explorer box (Ctrl-R will open and jump to it)
-Under the Microsoft Excel Objects, double-click the Sheet you are going to be working in.
-Paste the below code in.


Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 10 Then
    Range("A" & (Target.Row + 1)).Select
  End If
End Sub

You will need to paste this code in for *each* sheet that you require the carriage return. FYI, if you copy this sheet, the code will go along with it.

Is that what you were looking for?

RE: Carriage Return

Ken that worked like a charm. Thanks!

Thanks to everyone who helped.

RE: Carriage Return

If you don't want the user to have to highlight the entry range, then unlock the cells in the entry range (Format|Cells|Protection), then protect the worksheet (Tools|Protection|Protect Sheet).  Now the user will only be able to enter values in the correct range and can use Tab to go left to right, top to bottom to move between cells.  Using a color fill for the data entry range would also be helpful.

RE: Carriage Return

BTW, you can also look at a dataform, find it under Data/Form...

A more customizable -- but not built-in -- version is available from John Walkenbach's site.


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Carriage Return

Thanks Joerd. I never really looked at those before. It's nice to see I did almost the exact same thing for another project in VB. Glad I could reinvent the wheel.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close