Detecting changes in a single cell without worksheet_change?

[robotman]

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

 

[Chip Pearson]

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)

 

[bz]

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.

(e-mail address removed) remove ch100-5 to avoid spam trap

 

[robotman]

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