VBA number of events running

[Rmv]

Hi all! New to the forum.

I'm using the change event in a worksheet and when I initiate a change, it runs the code as expected. My code makes additional changes which triggers the change event code to run again since I leave application.events = true.

My question is how can I keep track whether I am in the original change event or if I'm in one of the sub change events which were triggered by the main one.

Thanks!

 

[crimson_b1ade]

I'm not sure I understand the question but you could add a MsgBox to the top of your procedures. That way you'll get a popup box on which code is running. Is this for debugging purposes? Example:

Sub SomeProcedure()
 
Msgbox "Now Running SomeProcedure"
 
your other code goes here
  
End Sub

 

[ZVI]

Hi & Welcome to the Board!

This is my understanding of issue with its solution, see details in the code's comments:

' Code of the sheet's module

Dim EventIsCharged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
  
  ' This is the key to skip the code replaying
  If EventIsCharged Then Exit Sub
  
  ' Disable events triggering
  Application.EnableEvents = False
  
  ' Set flag to not trigger of this subrotine again till the end of the code
  EventIsCharged = True
  
  ' Safe calling of some subroutines even with possible Application.EnableEvents = True
  MsgBox "Test() is calling"
  Call Test
  
  ' Reset the flag to allow triggering of this subroutine
  EventIsCharged = False
  
  ' Restore events
  Application.EnableEvents = True
  
  
End Sub

Sub Test()
  
  ' Let's assume that for some reason the events should be enabled
  Application.EnableEvents = True
  
  ' Do something with cells.
  Cells(1, 1).Formula = Cells(1, 1).Formula
  
  ' If EventIsCharged = True then the 1st line of Worksheet_Change() code will exit that sub
    
End Sub

Regards

 

[mikerickson]

Perhaps something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Static countOfChange As Long
    countOfChange = countOfChange + 1
    
    Rem your code
    
    countOfChange = countOfChange - 1
End Sub