Know which cell was just left
 

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

Know which cell was just left
 

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

Know which cell was just left
 

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

Know which cell was just left
 

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

Know which cell was just left
 

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

Know which cell was just left
 

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

Know which cell was just left
 

Jim,

Your three liner works perfectly.

thanks,
Jerry