Macros on exit

M

Manuel Murieta

Is there a way to run a macro on exiting a cell. For example if I answer Yes
in cell A4, then I would like it to automatically go to A10 on exiting A4.
 
G

Gord Dibben

Manuel

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A4")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = "yes" Then
Range("A10").Select
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP
 
J

JE McGimpsey

one way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Dim rOldActiveCell As Range

Private Sub Worksheet_Activate()
Set rOldActiveCell = ActiveCell
End Sub

Private Sub Worksheet_Deactivate()
Set rOldActiveCell = Nothing
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not rOldActiveCell Is Nothing Then
With Range("A4")
If Not Intersect(.Cells, rOldActiveCell) Is Nothing Then
If LCase(.Value) = "yes" Then
On Error Resume Next
Application.EnableEvents = False
Range("A10").Activate
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End With
End If
Set rOldActiveCell = ActiveCell
End Sub
 
M

Manuel Murieta

One more wrinkle here. What if I have several of these. For example here
you have a change in A4 moving the cursor to A10 when the word "yes" is put
in A4. What if I also want the same thing to happen in A10. In other words a
"yes" in A10 send the cursor to A20; a "yes" in A20 sends the cursor to A30,
a "no"in A20 sends the cursor to A30, and a "maybe" in A20 sends the cursor
to A40.

Thanks for the help here.
 
J

JE McGimpsey

One way would be to duplicate the code for A4 for the other cells.

If one has a large number of cells, it may be worth developing some
other method.
 
Top