Microsoft Access Office VB VBA Help and Examples
           

How to move to a different cell once a cell has been modified

It is often necessary when creating a spreadsheet for a user to control cursor movement around the screen. For instance if you have a rather large spreadsheet and after update of a cell (let's say G1) you need to go to cell MM1.

The simplest way to do this is to place the following code in to the worksheet i.e. Tools, Macro, VisualBasic Editor, then double click on the sheet (on the tree view on the left).

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'This procedure runs whenever a cell is changed by the user (not by recalculation) 
    If Target.Address() = "$G$1" Then 'Check the cell the user has changed 
        Range("$MM$1").Select 'move the cursor 
   End If 
End Sub

In the real world you would probably want to check more than one address. You could do this using a select case statement like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    Select Case Target.Address() 
        Case "$A$1" 
            Range("$G$1").Select 
        Case "$G$1" 
            Range("$MM$1").Select 
        Case "$MM$1" 
            Range("$A$1").Select 
    End Select 
End Sub
 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.