Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations MintJulep on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Define the location of the next cell

Status
Not open for further replies.

Ralph2

Industrial
May 3, 2002
345
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
 
Replies continue below

Recommended for you

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.
 
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).
 
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:
 
<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?
 
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
 
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.
 
You can set where the entry goes after pressing enter by going to Tools|Options.
 
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.
 
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..
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor