Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2015, 06:41 AM
Phil H Phil H is offline Macro to move focus after entry in a cell Windows XP Macro to move focus after entry in a cell Office 2003
Advanced Beginner
Macro to move focus after entry in a cell
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default 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).
Reply With Quote
  #2  
Old 06-18-2015, 10:04 AM
NoSparks NoSparks is offline Macro to move focus after entry in a cell Windows 7 64bit Macro to move focus after entry in a cell Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Have a look in the attached file. Right click the sheet tab and select view code.
Quote:
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).
this part will require you to hit enter twice in order to advance the selected cell as per normal.
Attached Files
File Type: xls Phil_H.xls (30.5 KB, 14 views)
Reply With Quote
  #3  
Old 06-18-2015, 11:05 AM
Phil H Phil H is offline Macro to move focus after entry in a cell Windows XP Macro to move focus after entry in a cell Office 2003
Advanced Beginner
Macro to move focus after entry in a cell
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 06-18-2015, 01:20 PM
NoSparks NoSparks is offline Macro to move focus after entry in a cell Windows 7 64bit Macro to move focus after entry in a cell Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
Reply

Tags
active cell, focus

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to move focus after entry in a cell Restrict Cell Entry Cardinal2 Excel Programming 1 01-30-2015 08:01 AM
Macro to move focus after entry in a cell Restricting Cell Entry to "N12345678" balbas662 Excel 3 11-27-2014 12:46 PM
Macro to move focus after entry in a cell 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft