PDA

View Full Version : Solved: Run macro after leaving a cell



Gunilla
08-17-2011, 05:45 AM
:help Problem: I want a macro to run when I leave a cell. The only thing I've found is EXIT and LostFocus, but it looks like those can only be used on forms. Do you have any suggestions.

Aflatoon
08-17-2011, 05:48 AM
Use the Worksheet_SelectionChange event. You will need to store a reference to the previous selection in a variable so that you can check it in the code, then update the variable to the current selection.
Alternatively, if you wanted to run the code when that cell is changed, rather than simply left, you can use the Worksheet_Change event.

Gunilla
08-17-2011, 11:18 PM
Thank you
I am not that familiar with targets and such. Can you give me an example. I only want the macro to run when I leave cell "A2" not to specify target cell. I don't want it to run if I change something in another cell. My other option is to link the macro to a button or something.:friends:

GTO
08-17-2011, 11:46 PM
Not tested. You'd probably want to add checking what cell is active upon the sheet becoming active after the workbook opens.

Option Explicit

Dim OldAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If OldAddress = Range("A2").Address Then
MyMacro
End If
OldAddress = ActiveCell.Address
End Sub

Private Sub MyMacro()
MsgBox " I just left A2", 0, ""
End Sub

mikerickson
08-18-2011, 12:00 AM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static RunMe As Boolean

If RunMe Then
Rem do something
Beep
End If

RunMe = (Target.Address = "$A$2")
End Sub

Aflatoon
08-18-2011, 12:18 AM
Just to be absolutely sure: you would like the code to run if you select A2, then select A3 without changing A2?

Gunilla
08-18-2011, 06:45 AM
Absolutly perfect!!
Thank you
:beerchug: