I have a sub I want to run each time a cell is changed. However, I need to
know which cell was active when the enter key is pressed. When I look at
active cell on Worksheet_Change it gives me the current cell with focus but
not the one the data was changed on.

thanks,
Jerry

Here is one way...

In a general VBA module in your workbook, add a Global variable (PrevCell in
this example):
Global PrevCell As Range

In the ThisWorkbook module, add the following event code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Free the object variable PrevCell
Set PrevCell = Nothing
End Sub

Private Sub Workbook_Open()
'Initialize the object variable PrevCell
Set PrevCell = ActiveCell
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error Resume Next
'Do something with PrevCell
MsgBox "PrevCell was " & PrevCell.Address
'Store the activecell as PrevCell
Set PrevCell = Target
End Sub

Hope this helps,

Hutch

"JerryH" wrote:

I have a sub I want to run each time a cell is changed. However, I need to
know which cell was active when the enter key is pressed. When I look at
active cell on Worksheet_Change it gives me the current cell with focus but
not the one the data was changed on.

thanks,
Jerry

Store it in a variable and update after you run your code:

Dim LastAddr
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

thisAddr = ActiveCell.Address

'your code

LastAddr = thisAddr

End Sub

--
Steve

"JerryH" wrote in message
...
I have a sub I want to run each time a cell is changed. However, I need to
know which cell was active when the enter key is pressed. When I look at
active cell on Worksheet_Change it gives me the current cell with focus
but
not the one the data was changed on.

thanks,
Jerry

Target is the cell that was changed in the Worksheet_Change event.
Paste this in a sheet module and change some cells...
'--
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub
--
Jim Cone
Portland, Oregon USA



"JerryH"

wrote in message
I have a sub I want to run each time a cell is changed. However, I need to
know which cell was active when the enter key is pressed. When I look at
active cell on Worksheet_Change it gives me the current cell with focus but
not the one the data was changed on.
thanks,
Jerry

Further...
Target can be more than one cell.
'--
Jim Cone

Cells(Target.Row,Target.Column)

If this post helps click Yes
---------------
Jacob Skaria


"JerryH" wrote:

I have a sub I want to run each time a cell is changed. However, I need to
know which cell was active when the enter key is pressed. When I look at
active cell on Worksheet_Change it gives me the current cell with focus but
not the one the data was changed on.

thanks,
Jerry

Jim,

Your three liner works perfectly.

thanks,
Jerry