I am wondering if anyone has an alternative way to detect a single cell
or column change without using the worksheet_change function?

For example, when I detect a change in one cell, I want it to change
the contents of several other cells. The problem is when it changes
the other cells then the whole worksheet_change subroutine is triggered
again and Excel gets caught up in itself.

Any ideas?!

Thanks!

John

John,

You need to use Application.EnableEvents = False in your Worksheet_Change
event procedure. When EnableEvents is False, Excel doesn't run any event
procedures. So, your code would look something like

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
''''''''''''''''''''''''''''''''
' Your Code Here
''''''''''''''''''''''''''''''''
Application.EnableEvents = True
End Sub

If you have On Error statements in your code, you should ensure that they
will cause EnableEvents to be restored to True.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am wondering if anyone has an alternative way to detect a single cell
> or column change without using the worksheet_change function?
>
> For example, when I detect a change in one cell, I want it to change
> the contents of several other cells. The problem is when it changes
> the other cells then the whole worksheet_change subroutine is triggered
> again and Excel gets caught up in itself.
>
> Any ideas?!
>
> Thanks!
>
> John
>

"(E-Mail Removed)" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> I am wondering if anyone has an alternative way to detect a single cell
> or column change without using the worksheet_change function?
>
> For example, when I detect a change in one cell, I want it to change
> the contents of several other cells. The problem is when it changes
> the other cells then the whole worksheet_change subroutine is triggered
> again and Excel gets caught up in itself.
>
> Any ideas?!
>
> Thanks!
>
> John
>
>

on error goto err

Application.EnableEvents = False
..... make your changes

err:Application.EnableEvents = True







--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

bz+(E-Mail Removed) remove ch100-5 to avoid spam trap

Excellent. This was driving me crazy every time I changed an cell
inside the Worksheet_Change sub.

The EnableEvents = False does the job.

Thank you!

John