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]

That's a fair point.

I hesitate to suggest solutions like this because you can start getting into scenarios that are difficult to trace, but you could use some sort of timing delay to skip the second run. I'm thinking to depend upon an onTime command to reset your isrunning variable. You could tell it to run no earlier than Now(). Then once all vba was done and the processor was freed up, it would run. Note that if you have a doevents command in your other code, that would trigger the ontime event immediately.

Because it always irks me when people code in the fashion that I have below, I will say that rather than directly using the Now() command to set the time to run the OnTime procedure, you should store the time in a global variable and then use that. There is no way to cancel an OnTime event unless you know the precise time that it was scheduled for (the time is stored as a double, so good luck guessing it!) This results because OnTime is write only, so you can never read anything to see what events are pending. You then can have your 'setfalse' sub also clear the time from the global time variable. And also add an if to make sure that you don't schedule another OnTime event when one is already pending. I have an application (that is distributed to me and is password protected) which schedules tens to hundreds of thousands of the same OnTime event in a matter of a couple minutes. The OnTime is used to turn screenupdating back on. So once the code is done running, the screen flashes for up to several days while the queue of events is run through. There are only 2 ways to stop the queue, that I'm aware of. 1) End task Excel and lose unsaved work 2) Add a stop line to your VBA code, since the OnTime queue for events that are waiting to run is cleared when the VBA editor pauses execution. This 2nd method does not clear future scheduled events, just ones that are already slated to run. I'm also guessing that this second method exploits an oversight in Microsoft's code, so they might 'fix it' at any time.

I got this to work... sheet1 code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If isRunning <> True Then
        isRunning = True
        Debug.Print "change" & Target.Address
        Application.OnTime Now(), "setfalse"
    End If
    
End Sub

Module1 code:

Public isRunning As Boolean

Sub setfalse()
    Debug.Print isRunning
    isRunning = False
    Debug.Print "I set it to false at " & Now()
End Sub

 

[xlsat]

Hi Rob, Very very interesting concept of using time! I hadn't thought on those lines at all... Let me digest this further and also try out how it fits into the scheme of things. Will do that and update back.

Thanks for the idea!

 

[RobExcel]

Sure. I hope it works.