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)


wrote in message
ps.com...
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

" wrote in
ps.com:

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.

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