Carriage Return 3

[FredGarvin]

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.

 

[IRstuff]

Can't you use HOME and DOWN?

TTFN

 

[MintJulep]

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.

 

[InspEngr]

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.

Regards,
-InspEngr

 

[InspEngr]

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.
-InspEngr

 

[FredGarvin]

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?

 

[IRstuff]

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



TTFN

 

[FredGarvin]

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.

Thanks!

 

[brengine]

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?
Ken

 

[FredGarvin]

Ken that worked like a charm. Thanks!

Thanks to everyone who helped.

 

[jghrist]

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.

 

[joerd]

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.

Cheers,
Joerd

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

 

[johnwm]

That would probably be

http://www.j-walk.com/ss/

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

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[FredGarvin]

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.