?row and ?key -- Worksheet_Change
 

Hi All,

I can programatically get the address after a sheet change
with the Worksheet_Change event such as the code below,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Debug.Print ActiveCell.Address(ReferenceStyle:=xlR1C1)

End Sub

But how can I get the row and col that the change occurred on?

And/or how can you tell what keystroke was used to exit the cell?

i.e. if you hit TAB to leave a cell the address method above returns
the correct row but the col is wrong and has been advanced. If you
hit the ENTER to leave a cell the address method above returns
the correct col but the row is wrong and has been advanced.

e.g. if you change R14C9 and hit TAB, .address returns R14C10
if you change R14C9 and hit ENTER, .address returns R15C9

and not knowing which key was used means you have no idea
what the changed row and col was? There must be a way.......

mtnw

?row and ?key -- Worksheet_Change
 

I think I found my own answer finally:

Target.AddressLocal(ReferenceStyle:=xlR1C1)

Let me know if this is wrong.

mtnw



"mtnw" wrote:

Hi All,

I can programatically get the address after a sheet change
with the Worksheet_Change event such as the code below,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Debug.Print ActiveCell.Address(ReferenceStyle:=xlR1C1)

End Sub

But how can I get the row and col that the change occurred on?

And/or how can you tell what keystroke was used to exit the cell?

i.e. if you hit TAB to leave a cell the address method above returns
the correct row but the col is wrong and has been advanced. If you
hit the ENTER to leave a cell the address method above returns
the correct col but the row is wrong and has been advanced.

e.g. if you change R14C9 and hit TAB, .address returns R14C10
if you change R14C9 and hit ENTER, .address returns R15C9

and not knowing which key was used means you have no idea
what the changed row and col was? There must be a way.......

mtnw

?row and ?key -- Worksheet_Change
 

Target may be a multi-cells selection, like:
$A1:B3,$D$9:B$12,$H14

You may test Target.Areas.count and Target.Count

HTH
--
AP

"mtnw" a écrit dans le message de news:
...
I think I found my own answer finally:

Target.AddressLocal(ReferenceStyle:=xlR1C1)

Let me know if this is wrong.

mtnw



"mtnw" wrote:

Hi All,

I can programatically get the address after a sheet change
with the Worksheet_Change event such as the code below,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Debug.Print ActiveCell.Address(ReferenceStyle:=xlR1C1)

End Sub

But how can I get the row and col that the change occurred on?

And/or how can you tell what keystroke was used to exit the cell?

i.e. if you hit TAB to leave a cell the address method above returns
the correct row but the col is wrong and has been advanced. If you
hit the ENTER to leave a cell the address method above returns
the correct col but the row is wrong and has been advanced.

e.g. if you change R14C9 and hit TAB, .address returns R14C10
if you change R14C9 and hit ENTER, .address returns R15C9

and not knowing which key was used means you have no idea
what the changed row and col was? There must be a way.......

mtnw