indi visual
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?
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?