How to get the correct active Cell position in Worksheet_Change

[Johan2000]

In a Excel Cell (A1), if we type 123 [ENTER]

then the code bellow will
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox ActiveCell.Address
End Sub

output A2 or B1 (depent on the direction of the cursor we set in
Tools/Option/Edit/Direction after selection (down, right,....)
Which is not what I wanted! (I wanted A1......)

question : How to get (to modify) the content of A1 (like a VALID clause in
VB)...
which we will doing a validation before leaving the cell..........

thanks so much!

 

[Gary''s Student]

You don't want ActiveCell.Address, you want Target.Address.

 

[Johan2000]

You don't want ActiveCell.Address, you want Target.Address.



--
Gary''s Student - gsnu200771

In a Excel Cell (A1), if we type 123 [ENTER]

then the code bellow will
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox ActiveCell.Address
End Sub

output A2 or B1 (depent on the direction of the cursor we set in
Tools/Option/Edit/Direction after selection (down, right,....)
Which is not what I wanted! (I wanted A1......)

question : How to get (to modify) the content of A1 (like a VALID clause in
VB)...
which we will doing a validation before leaving the cell..........

thanks so much!

MsgBox ActiveCell.Address // position after ENTER
MsgBox Target.Address // position before ENTER


You just make my day

Say thanks to Your GURU Gary!