Call Worksheet_Change macro in another worksheet

[Freddy]

Hi,

I have two worksheets, the first stores results from various tests. One test
is performed on every tenth specimen. When the data from this every tenth
test changes, a Worksheet_Change macro runs and pulls the data from the last
30 specimens into another worksheet where I analyze the data using a chart
and various SPC tests.

I have another Worksheet_Change macro in the SPC worksheet that will run
when the 30 test results are updated (manually). If the results fail any of
the SPC tests, an email is automatically sent.



My question - Is there a way to tie run the Worksheet_Change macro from the
SPC Worksheet at the bottom of the original Worksheet_Change? I have seen the
post "How to call a macro from another worksheet" on 7/12/06, but if I move
my Worksheet_Change macro from the SPC Worksheet to a General Module, it no
longer works when I manually update the data in teh SPC Worksheet.

Any ideas? Is it even possible to call one Worksheet_Change macro from
another Worksheet_Change macro?

Thanks,

Freddy

 

[Don Guillett]

How about putting the macro in a regular module and calling it from the
worksheet_change event OR the other macro?

 

[Freddy]

Hi Don,

I've already tried putting the second Worksheet_Change event and the send
email macro that goes with it module 1. I call to it at the bottom of my
first Worksheet_Change with:

Application.Run "75851_Plasticity_Last_30!Worksheet_Change"

When I run the first Worksheet_Change, I receive the error: The macro cannot
be found.

Any thoughts?

Thanks,

Freddy

 

[JLGWhiz]

Freddy, you have me confused. If you have a Worksheet_Change() macro, would
it not automatically run if you make a worksheet change? Why do you need to
call it from another sheet if it is tied to the sheet where the change
occurs? Is sounds to me like you are looking for a different kind of macro
that will be initiated based on a condition which can be controlled with an
"If ... Then ... Else" statement. What do you think, Don?

 

[Freddy]

Yes, I thought that it would automatically run when I started writing this
macro series.

The first Worksheet_Change macro on the first workbook works fine. The
second worksheet (where the first worksheet_change macro stores the last 30
data points) also has a Worksheet_Change macro that looks at certain test I
have set up. If the data passes the test, I have and IF formula issue a value
of 0, if it fails 1.

The second Worksheet_Change macro (that is attached to the second worksheet)
looks at the values from the If statement - if they are greater than 0. The
macro should send an email.

If I go to the second worksheet, and MANUALLY change the last datapoint so
the tests will fail, the email gets sent. It doesn't when I let the
Worksheet_Change macro from the original worksheet pull the data over to the
second worksheet.

 

[Freddy]

Can two macros run at the same time? I think it could be possible that the
first hasn't completely finished running when the second is starting.

Thanks,

Freddy

 

[JLGWhiz]

Worksheet_Change runs at the time a change occurs to the worksheet. If you
make three changes, it runs three times, each time as a change is made.