I'm trying to return the cell address of the cell a user changes on a sheet.

For example, if I want to capture changes on Sheet1, I put this code in the Sheet1 object:


Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox ActiveCell.Address

End Sub


If the user changes cell A1, I get two possible outcomes:

* if the checkmark is clicked in the formula bar (no tab or return), I get $A$1 (the correct address)

* if the user changes the cell and hits the return key, the response is $B$1

Obviously, the returned address is of the cell that is active after the return, not necessarily the one that was changed.

So, what is the correct way to code this so that the cell that is changed is the address that is returned?

Art

Hi Art,

Am Thu, 20 Aug 2015 09:36:12 -0700 (PDT) schrieb :

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox ActiveCell.Address

End Sub

try:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks, Claus!