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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,332
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

VBA Code:
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.
 
Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,332
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,455
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,332
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,455
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,687
Messages
5,720,298
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top