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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |