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]&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;0&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;1&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;2&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;3&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;4&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;5&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;6&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;7&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;8&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;9&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;10&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;11&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;12&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;13&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;14&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;15&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;16&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;17&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;18&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;19&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;20&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;21&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;22&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;23&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;24&#34&#60;br /&#62;
=COUNTIF(\$Z\$1,&#34;25&#34&#60;br /&#62;

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

&#60;br /&#62;
Private Sub Worksheet_Calculate()&#60;br /&#62;
If Range(&#34;Z38&#34 = 1 Then&#60;br /&#62;
Application.EnableEvents = False&#60;br /&#62;
'My Code&#60;br /&#62;
End If&#60;br /&#62;
End Sub&#60;br /&#62;
[/pre]

How do I code this worksheet_calculate sub correctly?

xld

Member
[pre]&#60;br /&#62;
Private Sub Worksheet_Calculate()&#60;br /&#62;
Dim cell As Range&#60;/p&#62;
&#60;p&#62; Application.EnableEvents = False&#60;/p&#62;
&#60;p&#62; For Each cell In Range(&#34;Z38:Z63&#34&#60;/p&#62;
&#60;p&#62; If cell.Value2 = 1 Then&#60;/p&#62;
&#60;p&#62; 'My Code&#60;br /&#62;
End If&#60;br /&#62;
Next cell&#60;/p&#62;
&#60;p&#62; Application.EnableEvents = True&#60;br /&#62;
End Sub&#60;br /&#62;
[/pre]

xld

Member
BTW, why are you using COUNTIF. I would use

=--(\$Z\$1=1)

etc