Change event on single cell fires many times

[XP]

I have the following code in a sheet module to capture the change event, when
the user changes cell contents:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" Then Call OtherProgram
End Sub

The problem is, when I edit cell E5 the program runs a long time, calling
the above routine over and over, presumably because my spreadsheet has many
formulas in it? Anyway, it seems to be looping a lot and it shouldn't. How
can I get it to stop this behaviour?

i.e. When the user edits E5 and presses Enter, it should run once. BTW, the
edited cell is not a formula, but contains a string. Many other cells are
linked to E5.

Thanks.

 

[Norman Jones]

Hi XP,

I suspect that your code differs from that whhich you show.

However, typically, to avoid recursive calls to the event procedure try
something like:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo XIT
Application.EnableEvents = False
'Your code

XIT:
Application.EnableEvents = True
End Sub
'<<=============

 

[XP]

Hi Norman, that did the trick thanks.

But my code does not differ: I copied it from my module...I'm curious, why
do you say you think it differs?

BTW, I will need to read your response tonight or tomorrow, I'm outta here!

Thanks again!

 

[Norman Jones]

Hi XP,

But my code does not differ: I copied it from my module...
I'm curious, why do you say you think it differs?

(1) Your code had the appearance of pseudo code

(2) I would not expect changes in formula dependent values to
fire the Worksheet_Change event

However, I am pleased that the conventional methodology of
deactivating and subsequently reactivating events has resolved
your problem.