Trigger macro on cell exit - possible?

[M0E-lnx]

Is it possible to trigger a macro when exiting / updating a cell in excel?

 

[ajetrumpet]

anything is possible, but i think you will find this to be too much trouble, and probably not worth it to do.

 

[boblarson]

As Adam has said, it might be a bit much, but in case you want to continue the attempt - look to the SelectionChange event of the Worksheet.

 

[M0E-lnx]

In that case, I'd better leave it alone.. it's probably more than I can handle.

Thanks

 

[Mike375]

As Bob said. something like

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

To put that in you right click on the sheet number at the bottom and then View Code.

Then you can do something like

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

With Target

With ActiveCell

If Range("D1") = 10 Then

Range("D2") = 20
Call Macro1
End If
End With
End With

End Sub

Worksheet_SelectionChange tries to run no matter where you click so you need conditions.

In the above case D1 needs to have a 10. The Macro1 also has a condition which is If ActiveCell.Value Like "0*" Then

Thus Macro1 will only run if D1 =10 and the cell clicked on has entry starting with 0

The line
Range("D2") = 20 simply puts 20 into D2

So to do what you want to do you would probably do something whereby when you clicked on the cell (the one where you want the macro to run when the cell looses focus) it would insert an entry in another cell and that would become a condition for when you clicked into another cell and the action would also clear the cell so as to be ready for next time. Thus with Worksheet_SelectionChange you can end up with a lot of conditions.

Basically, you should be able to do it with Worksheet_SelectionChange but it will probably need several conditions.

Here is another condition that might help give you the idea

With ActiveCell

If Range("D1") = 10 Then
Call Macro1
End If
If Target.Column = 5 And Target.Row = 25 Then
Range("L1") = 10
End If
If Range("L1") = 10 And Target.Column = 12 Then

Call Macro11
End If
End With
End With

As you cansee it is basically making an entry in a cell based on conditions and the entry in that cell then becomes the condition to something else such as running a macro.

 

[chergh]

People are on the right track but from your initial description you want to be using the "Worksheet_Change" event not the "Selection_Change" event.

If you are in cell A1 and move to Cell A2 the "target" parameter in the "Selection_Change" event will be A2. In the "Worksheet_Change" event the target parameter will be A1.

I would ignore the advice saying it's to much trouble as it's really not that hard once you know how to get to the events.

There is some good info on events at http://www.cpearson.com/excel/Events.aspx

 

[Mike375]

M0E-lnx

I just made a little thing here using Worksheet_SelectionChange. I acknowledge what Chergh posted but I thought I would just try and see if i could get the other to work

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("B1") = 100 Then
Macro1
End If
If ActiveCell.Value Like "0*" Then
Range("B1") = 100
End If
End Sub

And Macro1 is

Sub Macro1()
Range("B10") = "ZZZ"
Range("B1") = Null

End Sub

So if I click on a cell where the value starts with 0 Macro1 won't run because it will only run if B1 has an entry of 100. However, as you can see after the call of Macro1 does not work the next part inserts the 100 into B1.

If I now click on another cell (and hence leave the first cell) Macro1 will now run because B1 has the entry of 100. However, at the end of its run Macro1 clears B1 to Null. Thus Macro1 runs when I leave the cell.

I am only using Like "0*" because I am altering some existing stuff I made in Excel.

I shall now return to Access for some exotic stuff like OnLostFocus