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 |
| 