How to get the correct active Cell position in Worksheet_Change
 

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!

How to get the correct active Cell position in Worksheet_Change
 

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



--
Gary''s Student - gsnu200771


"Johan2000" wrote:

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!

How to get the correct active Cell position in Worksheet_Chang
 

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200771


"Johan2000" wrote:

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!