How to detect the event is trigger by end user? (ignore the event trigger by code)

user_11089411 0
Viewed 160

This question is from one of my customers who is developing an excel web add-in, they would like to detect whether the event is trigger by end-user or trigger by the code.

They have registered a Worksheet.onChanged event handler.

They would like to ignore the onChanged event change that triggers by their code themselves. and focus on Processing the event handler should only be done when the user changed the values.

Scenario: add-in listens to the sheetChange event. add-in user to enter values into the crosstabs, which the add-in will push to the server. However, add-in API can also do some modification on the crosstabs, for example, the add-in can enable the user single click and drill down to get more data, their API logic will fetch data from the service and write to the crosstabs. however, the sheetChanged event will be triggered as well.

The solutions that the customer has been tried:

First try: We tried to deregister the event handler before changing the values. We then registered again after having the values changed. This did not work as the deregistration is async and we were not able to await the deregistration.

Second try: context.runtime.enableEvents = false This is not possible as there are other events we are still interested in.

Current try: We store the address of the changed range before changing the values. In the onChanged handler, we compare the address. If the same we do not do the logic. After that, we delete the storage of the address again.

I also have tried to use a global flag and checking for that in the event handler, it doesn't work. here is my gist: https://gist.github.com/lumine2008/2b51d94d20cdca9ac9a0e97029dfd95c

office-js excel
2 Answers

Office.js doesn't provide such functionality yet.

Some workarounds, including a) context.runtime.enableEvents API, b) the global flag, or c) deregistering event before changing ranges and re-register event later, don't work cross-platform. They will over-suppress events in some scenarios, which means some events triggered by user operations will be suppressed as well.

The most reliable way is your "current try"

  1. Record the address of the to be changed range before changing the values.
  2. In onChanged event handler, compare the event args with the address you recorded before. If it matches, clear the record and exit the event handler.
user_103230250

You can make a variable that chechs if the user is editing.

for example:


Private Sub Worksheet_Activate()

    Static programChanging As Boolean

    programChanging = False

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If programChanging = False Then MsgBox("The User Is Changing")'*Do your code here*

End Sub

Private Sub Worksheet_SelectionChange(ByVal As Range)

    programChanging = True

    Range("A1").Value = "The Program Is Changing"

    programChanging = False

End Sub

You can use the programChanging variable to check if the user is changing instead the if the program is changing but the principle stay the same.

~~Edit~~

You can also use one of the cells as the variable like:


Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("A1").Value = "False" Then MsgBox("The User Is Changing")'*Do your code here*

End Sub

Private Sub Worksheet_SelectionChange(ByVal As Range)

    Range("A1").Value = "True"

    Range("B5").Value = "The Program Is Changing"

    Range("A1").Value = "False"

End Sub
user_87722990
Related