Change Event Endless loop :-(
 

Hi everybody: I have a macro that triggers other macros depending which
column has changed. The problem is that it enters in an endless because
the other macros change columns again and I enter an endless loop...

As you can see, this macro starts whith a change event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Long
C = Target.Column
Select case
Case is =1
Call Macro1
Case is =2
Call Macro2
End Select
End Sub

Sub Macro 1()
Range("a1").value=100
'Rest of the code here...
'As you can see, this macro change Column 1 so it triggers again the
change event (endless loop)
End Sub

Sub Macro 2()
Range("b1").value=200
'Rest of the code here...
'As you can see, this macro change Column 2 so it triggers again the
change event (endless loop)
End Sub

Can you suggest me any code to avoid the endless loop?

Thank you very much !

---

Change Event Endless loop :-(
 

Wrap your code within the Enableevents eg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Long
C = Target.Column

application.enableevents=false

Select case
Case is =1
Call Macro1
Case is =2
Call Macro2
End Select

application.enableevents=true

End Sub


--
Ivan F Moala


------------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...fo&userid=1954
View this thread: http://www.excelforum.com/showthread...hreadid=490447

Change Event Endless loop :-(
 

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Long
On Error GoTo Err_Handler
Application.EnableEvents = False
'...rest of your macro
Err_Handler:
Application.EnableEvents = True
On Error GoTo 0
End Sub

In article .com,
wrote:

Hi everybody: I have a macro that triggers other macros depending which
column has changed. The problem is that it enters in an endless because
the other macros change columns again and I enter an endless loop...

As you can see, this macro starts whith a change event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Long
C = Target.Column
Select case
Case is =1
Call Macro1
Case is =2
Call Macro2
End Select
End Sub

Sub Macro 1()
Range("a1").value=100
'Rest of the code here...
'As you can see, this macro change Column 1 so it triggers again the
change event (endless loop)
End Sub

Sub Macro 2()
Range("b1").value=200
'Rest of the code here...
'As you can see, this macro change Column 2 so it triggers again the
change event (endless loop)
End Sub

Can you suggest me any code to avoid the endless loop?

Thank you very much !

---

Change Event Endless loop :-(
 

Thank you very much botn IVAN F MOALA and JE McGimpsey . Your solutions
are perfect and I have solve the problem...

CONGRATULATIONS !!!