Worksheet_Change(ByVal Target As Range): How was the Target Range changed ?

[Jaafar Tribak]

Hi all,

Worksheet_Change(ByVal Target As Range)

The worksheet change event tells us which Range (Target) was edited but doesn't tell us how.

As we know, the user can change a cell either, by editing it with the keyboard, by pasting a value into it ,by drag and drop or by using the autofill feature... All of these actions trigger the worksheet change event but we can't tell which one is responsible for triggering it.

I think, having a "worksheet event" that tells us how the user modified the value in the cell would be an interesting feature that could be used in code to act differently depending on the action performed by the user.

This is a bit of a challenge question, but I will leave it there in case anyone comes up with any ideas.

Regards.

 

[jasonb75]

by drag and drop

For that one you could try comparing the target range to the selected range. Target tracks the source and destination cells while selection only tracks the destination.

 

[Jaafar Tribak]

For that one you could try comparing the target range to the selected range. Target tracks the source and destination cells while selection only tracks the destination.

Thanks jason.

That would probably involve using the selection change event along with the change event.

Drag-Move triggers the sheet change event twice: Once for the source range and once for the target range.

Drag-Copy (with CTRL key down) triggers the sheet change event once for the target range.

 

[Rick Rothstein]

I think, having a "worksheet event" that tells us how the user modified the value in the cell would be an interesting feature

First off, I don't think I have ever had a situation where I needed to know in what way a cell's value was changed, so I am not so sure how useful such an event would be. With that said, I do not believe you would be able to cobble together anything out of the events that are available because, as I understand it, VBA code does not run when Excel is in Edit Mode... so I'm thinking no matter how the change occurred, VBA procedures would not be able to see it. I could be wrong, but that is my guess.

 

[Jaafar Tribak]

First off, I don't think I have ever had a situation where I needed to know in what way a cell's value was changed, so I am not so sure how useful such an event would be. With that said, I do not believe you would be able to cobble together anything out of the events that are available because, as I understand it, VBA code does not run when Excel is in Edit Mode... so I'm thinking no matter how the change occurred, VBA procedures would not be able to see it. I could be wrong, but that is my guess.

I agree. This is more of a curiosity question than anything.

I am writing some code (still at draft stage) which kind of works despite excel being in edit mode.

The key is to have the code running (out of process) like in a hidden excel instance (similar to what I did here) and from there, remotely monitor the text displayed in the current application status.

Each time the user performs a paste, a drag & drop or autofill, the status bar text changes... Capturing that text can conveniently be used to differentiate between the user's actions... MSAAccessibility is ideal for monitoring the changing status bar text.

I tried monitoring the state of the mouse cursor (which changes when autofilling or when drag & dropping) instead of monitoring the status bar text but, that didn't work as those cursors are not system cursors. This is a shame because it would have been much cleaner and more reliable.

Still having some problems. If I get this working consistently, I will post the code here.

Regards.

 

[Rick Rothstein]

I have seen you do some amazing things with your subclassing and hooking so I don't doubt that, if there is a way to do this, you will find it.