Worksheet_Change Event "Circular Reference"

[Vyyk Drago]

Hi,

I have an interesting problem. I have a worksheet where
I want to be able to make the action of changing the
value of cell A1, cause the value of B1 display double
the value of A1. If I type a value in B1 I want A1 to
display half the value of B1 (Actually the calculation is
much more complicated, but just for testing purposes any
relational calculation will do.) When I use the
Worksheet_Change event, it captures the value changes in
one cell and runs the calculation, but that then changes
the value of the adjacent cell which then runs the code
again, etc. I have found that after approx. 220
iterations it finally stops, but this can cause loads of
other problems for me.

To put it simply, how can I tell the worksheet_change
event not to fire if code run from that event causes
other cells to change. Here is a sample of my code below:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim v As Variant
Static x As Long

x = x + 1

Select Case Target.Column
Case 1
With Target
.Font.Bold = True
.Offset(0, 1).Font.Bold = False
.Offset(0, 1) = Target * 2
End With
Case 2
v = Application.Caller
Debug.Print v
With Target
.Font.Bold = True
.Offset(0, -1).Font.Bold = False
.Offset(0, -1) = Target / 2
End With
End Select

Application.StatusBar = x

End Sub

Please note that the x variable is used to check how many
times this event runs before it stops and I have no idea
on how to use the Caller property. I thought that maybe
it could be used to see if the event called itself and
therefore be used to tell it to stop.

Please help...this is driving me nuts.
Many thanks
Vyyk

 

[Bob Phillips]

Vyyk,

Disabling events will stop the automatic triggering of any subsequent
events. This can be done with
Application.EnableEvents = False

Just make sure you reset to True on exit from the procedure.

As a further fail-safe, it is best to trap any errors, so that you don't
bomb out of the procedure with events disabled. So in summary, the procedure
would look like

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim v As Variant
Static x As Long

Application.EnaableEvents = False
On Error Goto ws_exit

x = x + 1

Select Case Target.Column
Case 1
With Target
.Font.Bold = True
.Offset(0, 1).Font.Bold = False
.Offset(0, 1) = Target * 2
End With
Case 2
v = Application.Caller
Debug.Print v
With Target
.Font.Bold = True
.Offset(0, -1).Font.Bold = False
.Offset(0, -1) = Target / 2
End With
End Select

Application.StatusBar = x

ws_exit:
Application.EnableEvents = True

End Sub

 

[Vyyk Drago]

Cheers for that Bob! Excellent!

-----Original Message-----
Vyyk,

Disabling events will stop the automatic triggering of any subsequent
events. This can be done with
Application.EnableEvents = False

Just make sure you reset to True on exit from the procedure.

As a further fail-safe, it is best to trap any errors, so that you don't
bomb out of the procedure with events disabled. So in summary, the procedure
would look like

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim v As Variant
Static x As Long

Application.EnaableEvents = False
On Error Goto ws_exit

x = x + 1

Select Case Target.Column
Case 1
With Target
.Font.Bold = True
.Offset(0, 1).Font.Bold = False
.Offset(0, 1) = Target * 2
End With
Case 2
v = Application.Caller
Debug.Print v
With Target
.Font.Bold = True
.Offset(0, -1).Font.Bold = False
.Offset(0, -1) = Target / 2
End With
End Select

Application.StatusBar = x

ws_exit:
Application.EnableEvents = True

End Sub


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks





.