Operation that triggered Change event & call-iteration #

[xlsat]

To know the operation that triggered Change event & call iteration # to the same event
My basic requirement is that I want to know 2 things when inside a “Worksheet_Change” event :

#1 – What operation has caused this event to be triggered
#2 – Whether this is the first call or second call to this event due to that operation.

Some background:
My requirement revolves around doing an Undo operation inside the Worksheet_Change based on certain conditions. And, of course I need to do it only once for one instance of an operation. The Worksheet_Change event gets called either once or twice depending on the operation. For e.g.

Operation - # of calls made to Worksheet_Change
------------------------------------------------
Edit a cell directly – 1
Delete a cell – 1
Copy and Paste – 1
Cut and Paste – 2
Drag Drop – 2

Hence I need to know exactly the iteration # of the call to the Worksheet_Change event for that particular operation which triggered it in the first place. I am trying to use the combination of Worksheet_SelectionChange event and Worksheet_Change event and creating a counter to track the call #. Since, ideally, when we do a say for e.g., a cut-paste, after the cut is made, Worksheet_SelectionChange is called once and Worksheet_Change is called twice. So, I can use the SelectionChange event to reset the counter, or so I thought. But the following are some of the pitfalls:
A. There doesn’t seem to be any property from which we can know directly which of the above kind of operations has triggered the Change event. (The closest I saw is Application.CutCopyMode, which gives only the info of whether a cut or a copy-paste has been done and that too only inside the Worksheet_SelectionChange event).
B. Several conditions allow Excel to just get into the Worksheet_Change event without necessarily calling Worksheet_SelectionChange and hence the counter logic goes for a toss.
B.1. After a cut-paste operation (which say calls Application.Undo based on the condition), if a Redo is done, the Worksheet_Change is called without a call to Worksheet_SelectionChange event.
B.2. The same thing happens, if an operation is repeatedly done, for e.g. Copy, Paste, Paste, Paste!

So, I wanted to understand if there is any other way of getting the 2 items of information as stated above. Or by chance is there a direct attribute which can give this? For sure, Excel has all these information while it runs the event. Does it care to share

 

[RobExcel]

I don't know of a way to gather the information on what specific action caused the change event or what iteration it might be.

You can often get around the duplicate change call by creating a global variable that indicates that code is running. You then check that variable in your change code to see if there's already a change running. If it is already running, you just exit the routine.

I'd note that for a cut/paste, there really are 2 changes. One to cut and a second to paste. You can tell the difference, within a cut/paste operation by the target range. The following code can be inserted on a sheet to demonstrate this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "change" & Target.Address
End Sub

If some identification of target cells or use of an IsRunning global variable don't work for your application, you could try storing some information as to what items have been undone in the same location that you are storing your undo information.

 

[xlsat]

Thanks Rob.

I did try using the Target cells (by comparing Target.Address with Selection.Address) to find out which iteration it was in using a global variable. But the counter management is getting skewed because of operations like Drag-Drop, Redo, multiple-paste's, etc.

You have mentioned about IsRunning global variable. Is there a way to reset it to 0 (or False) when any operation is done by the user and the macro starts running? If so, in which event could it be done? I was trying to do this kind of reset in Worksheet_SelectionChange event which I found is not foolproof for reasons mentioned in my earlier post. So, is there any other place to do this? If so, it will probably make this a whole lot easier.

 

[Norie]

If you just want the code to be triggered once, use Application.EnableEvents = False.

 

[RobExcel]

Norie's solution is simpler that an IsRunning variable, as long as you don't want other events to fire.

In either solution, you'd need to either turn events back on or reset the variable when you were done running code. You just do either in your last executed line of code.

 

[Norie]

Rob

Oops, forgot to mention turning events back on again.

Kind of essential.

 

[RobExcel]

Eh, it becomes obvious pretty fast when your events no longer do anything

 

[Warship]

This may be a bit out there but maybe for the Cut/Copy/Paste ops, create your own macro to do those and track the operation there? If I'm not mistaken when you asign a shortcut key to a macro, it overrides the system's so you could still use the XCV keys. And maybe monitor keypress for the deleting. I have nothing for th drag-n-drop.

 

[RobExcel]

Interesting, Warship.

It seems tricky to catch all the ways someone might do various operations. Especially with right click context menus, the ribbon, etc. It also wouldn't catch it if a macro did the execution, but maybe that's desirable.

 

[Warship]

Could always take away the Ribbon and context menu

It also wouldn't catch it if a macro did the execution

But then you'd know