×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Define the location of the next cell

Define the location of the next cell

Define the location of the next cell

(OP)
Somewhat (?) off topic...
I have a spread sheet (Excel) with 4 columns. As I enter data and hit enter I move to the next cell. But... at the end I want to move down 1 row and back to the first row. Currently to do this I hit the down arrow and home keys. This is time consuming and I wonder if there is some way to define where my sheet stops and force a down and return.
Thanks for any advice
Ralph

RE: Define the location of the next cell

I think that your need will be met by selecting your data area before you begin entering your data values.  Then your movements after <enter> are restricted to being inside the selected area.

RE: Define the location of the next cell

For my many "standard" worksheets I "unlock" the input cells (cells that accept input variables) and colour them, then protect the worksheet. On a protected worksheet the Tab key will cycle through the unlocked (unprotected) input cells.

Unlock cells through the formatting dialog. Protect the worksheet through the Tools menu.

I colour the unlocked cells so that I can visually tell them apart. I print with the "black and white" page setup option so that the printed page is legible (no gray background).

RE: Define the location of the next cell

If you want to do it in code:

CODE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then
Cells(Target.Row + 1, 1).Select
End If
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

RE: Define the location of the next cell

(OP)
<quote> Denial (Structural)      
31 Mar 05 16:20
I think that your need will be met by selecting your data area before you begin entering your data values.  Then your movements after <enter> are restricted to being inside the selected area.<end quote)

Interesting...How do you do this?

RE: Define the location of the next cell

My terminology might be a bit suspect here, but ...

"Highlight" your rectangular block of cells the same way that you would normally highlight a range (eg dragging the mouse diagonally across the screen whilst holding the left button down).  You will notice that the top-left cell of the highlighted range is still individually "selected", and if you type a character on your keyboard it will be placed in that top-left cell.  When you have completed your entry for that cell, hit <enter>.  The selection will move to the next "highlighted" cell (either downwards or to the right according to your edit options) but the entire highlighted block remains highlighted.

If you end the data entry for a cell with an arrow-key instead of the <enter> key, you destroy the highlighting so the method does not work.

HTH

RE: Define the location of the next cell

My Excel seems to be set up a bit differently than yours, but I'm sure yours could be adjusted.  I use tab to move between columns and enter to move between rows.  It "remembers" what what column you started your tab sequence in and when you hit enter, it moves you to the next row, same starting column.

Denial's method works pretty slick too... give 'em both a shot.

RE: Define the location of the next cell

You can set where the entry goes after pressing enter by going to Tools|Options.

RE: Define the location of the next cell

That should be Tools|Qptions|Edit.

RE: Define the location of the next cell

You might want to take a look under the menu Data/Form...

Cheers,
Joerd

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

RE: Define the location of the next cell

(OP)
Thanks to all.. Project is finished. Protecting the sheet worked... But because some of my entries required data in that "last" cell it became cumbersome to unlock on those few occasions. Eventually used it all in the default MS way.
Again, thanks to all that offered sugestions..

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!


Resources