• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Worksheet Calculate Sub (Monitoring Multiple Events)

indi visual

New Member
I am trying to produce a Worksheet_Calculate event that monitors multiple events.


I have the following cells Z38:Z63 which contain the following formulas (they all reference Z1):

[pre]<br />
=COUNTIF($Z$1,"0")<br />
=COUNTIF($Z$1,"1")<br />
=COUNTIF($Z$1,"2")<br />
=COUNTIF($Z$1,"3")<br />
=COUNTIF($Z$1,"4")<br />
=COUNTIF($Z$1,"5")<br />
=COUNTIF($Z$1,"6")<br />
=COUNTIF($Z$1,"7")<br />
=COUNTIF($Z$1,"8")<br />
=COUNTIF($Z$1,"9")<br />
=COUNTIF($Z$1,"10")<br />
=COUNTIF($Z$1,"11")<br />
=COUNTIF($Z$1,"12")<br />
=COUNTIF($Z$1,"13")<br />
=COUNTIF($Z$1,"14")<br />
=COUNTIF($Z$1,"15")<br />
=COUNTIF($Z$1,"16")<br />
=COUNTIF($Z$1,"17")<br />
=COUNTIF($Z$1,"18")<br />
=COUNTIF($Z$1,"19")<br />
=COUNTIF($Z$1,"20")<br />
=COUNTIF($Z$1,"21")<br />
=COUNTIF($Z$1,"22")<br />
=COUNTIF($Z$1,"23")<br />
=COUNTIF($Z$1,"24")<br />
=COUNTIF($Z$1,"25")<br />



At any given time any one of these cells (Z38:Z63) will contain a '1' and the rest '0'


Although I got this to work, it only fires off once (and only for Z38), and then it no longer works afterward.


I need the code below accordingly with something along the lines of... If


if 'Z38' = "1", then run macro

if not then do nothing and run next,

if 'z39' = "1", then run macro

if not then do nothing and run next,

if 'z40' = "1", then run macro

if not then do nothing and run next,....etc so on and so forth


<br />
Private Sub Worksheet_Calculate()<br />
If Range("Z38") = 1 Then<br />
Application.EnableEvents = False<br />
'My Code<br />
End If<br />
End Sub<br />
[/pre]


How do I code this worksheet_calculate sub correctly?
 

xld

Member
[pre]<br />
Private Sub Worksheet_Calculate()<br />
Dim cell As Range</p>
<p> Application.EnableEvents = False</p>
<p> For Each cell In Range("Z38:Z63")</p>
<p> If cell.Value2 = 1 Then</p>
<p> 'My Code<br />
End If<br />
Next cell</p>
<p> Application.EnableEvents = True<br />
End Sub<br />
[/pre]
 
Top