#1
|
|||
|
|||
Macro to move focus after entry in a cell
Two ranges: D7:G57 and J7:M57
Any time an entry is made in a G cell, focus returns to the next lower D cell. Same for M entries – focus moves to the next lower J cell. For example: When an entry is made in G35, the active cell (focus) changes to D36. However, when an entry is made in G57 (or M57), the last cell in the range, this rule does not apply – the active cell remains G57 (or M57). |
#2
|
|||
|
|||
Have a look in the attached file. Right click the sheet tab and select view code.
Quote:
|
#3
|
|||
|
|||
NoSparks,
Thanks for your time and the solution to my query. It works perfectly. Now, users are asking if possible to have focus move across the range. For example: a date is entered in column D, an entry from a dropdown in column E (data validation list), a customer name is typed in column F, and a dollar value entered in column G. Problem is, focus moves down after entries in columns D, E, and F, not across - Excel default. Can you modify the code to move across the row, and leave the move after the G entry as is? Next, after an entry in G57 (last cell in the first range), have focus move to J7 (first cell in the second range)? These moves will save users much time by not having to use the mouse to move to the next cell as they post. Guess I should have anticipated this. Mea culpa. Thanks, Phil |
#4
|
|||
|
|||
Most of what you ask I would never notice, in Excel options I have the direction of move after enter set to Right. This just suits most things I do.
Wish that setting would stick with individual workbooks rather than overall Excel. Anyhow this should look after what you ask. Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("D7:G57,J7:M57")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub 'first range If Target.Column = 4 Then Range("E" & Target.Row).Select If Target.Column = 5 Then Range("F" & Target.Row).Select If Target.Column = 6 Then Range("G" & Target.Row).Select If Target.Column = 7 Then If Target.Row = 57 Then Range("J7").Select Else Range("D" & Target.Row + 1).Select End If End If 'second range If Target.Column = 10 Then Range("K" & Target.Row).Select If Target.Column = 11 Then Range("L" & Target.Row).Select If Target.Column = 12 Then Range("M" & Target.Row).Select If Target.Column = 13 Then If Target.Row = 57 Then Range("M" & Target.Row).Select Else Range("J" & Target.Row + 1).Select End If End If End Sub |
Tags |
active cell, focus |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Restrict Cell Entry | Cardinal2 | Excel Programming | 1 | 01-30-2015 08:01 AM |
Restricting Cell Entry to "N12345678" | balbas662 | Excel | 3 | 11-27-2014 12:46 PM |
Trying to update a table cell with a value based on a drop down box entry | mkasem | Word VBA | 2 | 09-29-2013 08:36 PM |
Macro - Can I move information from a website and excel to word? | redzan | Word VBA | 1 | 03-13-2013 07:39 AM |
Move data from 1 cell to another cell | Catalin.B | Excel | 1 | 06-25-2011 12:51 PM |