CODEWITHSUNDEEP
exceleventsvba
winwaed
winwaed

Reputation: 7801

Is it possible to automatically disable the Excel Worksheet_Change event whilst processing the same event?

I have the following code that is connected to the InputSheet's Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
  ' First, make sure we're working with an InputRange cell
  ' ALL OTHER CELLS: Exit, no action
  Dim rngCells As Range
  Set rngCells = Intersect(Target, InputSheet.Range("InputRange"))
  If rngCells Is Nothing Then
       Exit Sub
  End If

  ' Do interesting stuff that updates InputSheet
End Sub

This code is only designed to handle a subset of cells (InputRange), but updates other cells outside of this range. Each of these updates (and there can be a few hundred) will fire the Worksheet_Change event.

Is there any way of disabling this during execution, and then re-enabling at the end?

Eg. some languages have function calls to do this, or even in VBA a timer event can be disabled by pausing the timer.

I could add a simple flag, and this would avoid the Intersect call. No doubt this would give a good speed improvement, but it would still result in the unnecessary event callbacks.

Upvotes: 3

Views: 65

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

Yes: 

Application.EnableEvents = False

Then when you are done with the procedure:

Application.EnableEvents = True

Upvotes: 5

Related Questions