How to temp disable Worksheet_Change Event while code in same completes?

[EagleOne]

2003/2007

My WorkSheet_Change code is looping because each change restarts it.

How does one permit the code to process without restarting the loop?

My code follows:

********************************
'PLACED IN THE GENERAL MODULE:
'Public X As Long
'Function OrigRows() As Long
' OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count
'End Function

'PLACED IN THE WORKSHEET MODULE:
Private Sub Worksheet_Activate()

X = OrigRows 'calls the Function OrigRows() in the General Module to value "X"

End Sub

'PLACED IN THE WORKSHEET MODULE:
Private Sub Worksheet_Change(ByVal Target As Range)

If Sheets("Trial Balance Current").UsedRange.Rows.Count > X And X > 0 Then
Dim myRange As Range
Sheets("Trial Balance Current").Unprotect Password:="xxxxx"
Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
' Column "A" Diocese Line Map
myRange(1).Formula = myRange(1).Offset(-1, 0).Formula 'CODE BEGINS LOOPING HERE!!
' Column "B" Diocese Category
myRange(2).Formula = myRange(2).Offset(-1, 0).Formula
' Column "C" Diocese Line description
myRange(3).Formula = myRange(3).Offset(-1, 0).Formula
' Column "D" G/L Account Number
myRange(4).Locked = False
myRange(4).FormulaHidden = False
' Column "E" Account Name
myRange(5).Locked = False
myRange(5).FormulaHidden = False
' Column "F" Current Year Real G/L $ Balance
myRange(6).Formula = myRange(6).Offset(-1, 0).Formula
' Column "G" Current Year account $ balance Per report
'myRange(7).Locked = False
'myRange(7).FormulaHidden = False
' Column "H" Prior Year Real G/L $ Balance
myRange(8).Formula = myRange(8).Offset(-1, 0).Formula
' Column "I" Prior Year Real G/L $ Balance
'myRange(9).Formula = myRange(9).Offset(-1, 0).Formula
' Column "J" SRP Report Line Mapping
myRange(10).Formula = myRange(10).Offset(-1, 0).Formula
' Column "K" Public Report Line Description
myRange(11).Formula = myRange(11).Offset(-1, 0).Formula

Sheets("Trial Balance Current").Protect Password:="xxxxx", DrawingObjects:=False, _
Contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
X = Sheets("Trial Balance Current").UsedRange.Rows.Count
End If

End Sub


Obvoiusly I thought that the changes made in the ChangeEvent code would not loop on itself.

How can I work around the issue? I am new to worksheet events VBA code as it shows.

Any help appreciated! EagleOne

 

[Charlie]

This one you will be using all the time:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

(yada, yada...)

Application.EnableEvents = True

End Sub

 

[Bob Phillips]

Add a fail-safe error handler

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Goto ws_exit
Application.EnableEvents = False

(yada, yada...)

ws_exit:
Application.EnableEvents = True

End Sub

 

[EagleOne]

You will not believe this. You had helped me earlier therefore I had just looked back at your code
again. I noticed the code "Application.EnableEvents = False."

You beat me to acknowledge your earlier help in this thread.

As always, you are a tremendous help in the Excel Forums. Thanks!

 

[Charlie]

Good point. Mine is usually one of the Yada's