Know which cell was just left

[JerryH]

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

 

[Tom Hutchins]

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

 

[AltaEgo]

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

 

[Jim Cone]

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"
<[email protected]>
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

 

[Jim Cone]

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

 

[Jacob Skaria]

Cells(Target.Row,Target.Column)

If this post helps click Yes

 

[JerryH]

Jim,

Your three liner works perfectly.

thanks,
Jerry