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


"Vyyk Drago" wrote in message
...
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


.