On Friday, 15 November 2013 17:37:12 UTC+11, Claus Busch wrote:
Hi Howard,



Am Thu, 14 Nov 2013 17:55:28 -0800 (PST) schrieb Howard Silcock:



I have now played with this and found a small problem. Each time the Worksheet_Change macro enters the new value in a cell, this is detected as a new change and the macro is launched again, this time changing the value in the other cell, and so on. When I tracked the process, it stopped after 95 iterations.



I noticed it because I accidentally typed a slightly incorrect value in place of 2.54 in one of the statements in the code, so the second conversion no longer changed the first cell back to its original value again. This made the values in A1 and B1 oscillate around for a while before settling down to two values, neither of them equal to the value that should be there. Normally you probably wouldn't notice this happening as the cells are always overwritten with the same values over and over.



Is there some way of preventing calls to the Worksheet_Change macro being initiated from within the macro itself?



try:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

On Error GoTo ERRHANDLER

Select Case Target.Address(0, 0)

Case "A1"

Range("B1") = 2.54 * Target

Case "B1"

Range("A1") = Target / 2.54

End Select

ERRHANDLER:

Application.EnableEvents = True

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Yes, that works. Thank you.

Howard