Home |
Search |
Today's Posts |
#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("A1:D50") '<<========= 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("A1:D50") 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Return | Excel Discussion (Misc queries) | |||
Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions | |||
How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions |