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.

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
'<<=============


---
Regards,
Norman



"XP" <(E-Mail Removed)> wrote in message
news:3BA3FF12-764A-4A1A-BABC-(E-Mail Removed)...
>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.

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" wrote:

> 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
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
>
> "XP" <(E-Mail Removed)> wrote in message
> news:3BA3FF12-764A-4A1A-BABC-(E-Mail Removed)...
> >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.
>
>
>

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.


---
Regards,
Norman