Ways to activate a macro

[Guest]

Is there a way to cause a macro to execute when a certain cell is exited? My
end goal is described in an additional post: How can I force certain text
formatting in a cell?

 

[Oliver Ferns via OfficeKB.com]

Yes....set up a private global variable in sheet code like this

Private strLastCell As String

Then use this code (changing "$A$1" to whatever cell you want).....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If strLastCell = "$A$1" Then
Call yourmacroname
End If
Let strLastCell = Target.Address
End Sub


This won't work the first time you exit the cell as the global variable is
set after the code has run so you need to add this code as well (replacing
"$A$1" again...

Private Sub Worksheet_Activate()
Let strLastCell = "$A$1"
End Sub



Having said all this, I cannot think of a situation where this is useful,
but hey!

Hope this helps...
Oli

 

[Oliver Ferns via OfficeKB.com]

Hi,
the Worksheet Activate event code should read this...

Private Sub Worksheet_Activate()
Let strLastCell = ActiveCell.Address
End Sub

otherwise the code will fire from the first cell you "exit"
Cheers,
O