A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Auto-return"



 
 
Thread Tools Display Modes
  #1  
Old September 11th, 2005, 04:57 PM
Jaytee
external usenet poster
 
Posts: n/a
Default "Auto-return"

Question 1: If I'm entering rows of data, can I set it up at a certain column
to return to the start of the next row?
So if I have 4 columns of data, I input in A2, hit enter to get to B2, then
input and hit enter to get to c2, input and enter to get to d2, input and
enter to get to b3.
  #2  
Old September 11th, 2005, 05:20 PM
Paul B
external usenet poster
 
Posts: n/a
Default

Jaytee, here is some code that will do it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column 5 Then Exit Sub
Target.Offset(1, -3).Select
End Sub

To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

To change the security settings go to tools, macro, security, security level
and set it to medium


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Jaytee" wrote in message
...
Question 1: If I'm entering rows of data, can I set it up at a certain

column
to return to the start of the next row?
So if I have 4 columns of data, I input in A2, hit enter to get to B2,

then
input and hit enter to get to c2, input and enter to get to d2, input and
enter to get to b3.



  #3  
Old September 11th, 2005, 05:28 PM
Ragdyer
external usenet poster
 
Posts: n/a
Default

Some things you can try:

If you start entering data in B2, hit Tab to move right each time.
When you come to the end of the set (E2), hit Enter, and the focus moves
to C2 automatically (cell under the first cell you used Tab on).

OR

You could click and drag and create a selected range, where the first cell
of data entry has the focus (colored white).
Now, hold down the Enter key, and you'll see that the focus moves down and
to the right, but *remains* within the selection.
Hold Tab, and the focus moves right and then down, remaining within the
selection.

OR

Select a cell, hold Ctrl, and click in other non-adjacent cells, making a
non-contiguous range.
Hitting Enter or Tab will now move the focus to each cell, in the
*order* that you selected them.

In fact, you can name and save these selections to be used for filling
forms.

Check out this old post about these "named ranges".

http://tinyurl.com/39vzv

Another option is to unlock the cells that you wish to accept data entry,
and then protect the sheet.

When you hit Tab on a protected sheet, the focus will move from
unprotected cell to unprotected cell.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jaytee" wrote in message
...
Question 1: If I'm entering rows of data, can I set it up at a certain

column
to return to the start of the next row?
So if I have 4 columns of data, I input in A2, hit enter to get to B2,

then
input and hit enter to get to c2, input and enter to get to d2, input and
enter to get to b3.


  #4  
Old September 11th, 2005, 06:00 PM
Norman Jones
external usenet poster
 
Posts: n/a
Default

Hi Jaytree,

"Jaytee" wrote in message
...
Question 1: If I'm entering rows of data, can I set it up at a certain
column
to return to the start of the next row?
So if I have 4 columns of data, I input in A2, hit enter to get to B2,
then
input and hit enter to get to c2, input and enter to get to d2, input and
enter to get to b3.


If you want a VBA solution, try:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngInput As Range
Dim i As Long

Set RngInput = Range("A150") '========= CHANGE
i = RngInput.Columns.Count

If Target.Cells.Count 1 Then Exit Sub

If Not Intersect(Target, RngInput) Is Nothing Then
If Target.Column = RngInput.Columns(i).Column Then
Target.Offset(1, 1 - i).Select
Else
Target.Offset(0, 1).Select
End If
End If
End Sub
'===================

Change Range("A150") to match the required input range.

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

************************************************** **********
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
************************************************** **********

If you are not familiar with macros you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto resize, auto realign, auto ANYTHING driving me crazy [email protected] Powerpoint 2 April 30th, 2005 02:14 AM
How to cancel a return receipt? Carl General Discussion 0 April 25th, 2005 05:08 AM
Getting Auto Text with F3 with trailing text in document Alan Zarky General Discussion 1 April 23rd, 2005 09:30 AM
restoring auto correct file back to original word version Sharon G General Discussion 4 August 8th, 2004 02:37 PM
summing based on auto filter Art General Discussion 2 June 13th, 2004 04:19 PM


All times are GMT +1. The time now is 05:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.