Worksheet_Calculate Running on Every Sheet
 

I have a Worksheet_Calculate function defined in one sheet. It runs on all
sheets in every open file. How can this be? If the code is in the sheet,
how can it be running in other sheets, let alone other workbooks? Is this a
bug? Is it supposed to work that way? How can I make it work the way I
expect it to?

Thanks,

Mike.

Worksheet_Calculate Running on Every Sheet
 

Mike,

The worksheet_calculate event is only fired when the containing worksheet is
calculated.

But when excel calculates it recalculates all open workbooks, and any sheet
in any open workbook that 'needs' recalculating will get recalculated
(possibly more than once),

Things that make a worksheet 'need' recalculating include:
- contains a volatile function
- contains formulae that are dependent on cells (on this worksheet or
others) that are volatile, changed or have been recalculated
- some other worksheet changes

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"mkelly4ca" <u20439@uwe wrote in message news:5e3bd5c32cd15@uwe...
I have a Worksheet_Calculate function defined in one sheet. It runs on all
sheets in every open file. How can this be? If the code is in the sheet,
how can it be running in other sheets, let alone other workbooks? Is this
a
bug? Is it supposed to work that way? How can I make it work the way I
expect it to?

Thanks,

Mike.

Worksheet_Calculate Running on Every Sheet
 

What you are describing leads me to suspect that you have a volatile function
somewhere on your worksheet. Volatile functions like Now and Indirect
recalculate every time a calculation is run anywhere... Hence your code is
executing every time.

For more on volatile functions check this out.

http://www.decisionmodels.com/calcsecretsi.htm
--
HTH...

Jim Thomlinson


"mkelly4ca" wrote:

I have a Worksheet_Calculate function defined in one sheet. It runs on all
sheets in every open file. How can this be? If the code is in the sheet,
how can it be running in other sheets, let alone other workbooks? Is this a
bug? Is it supposed to work that way? How can I make it work the way I
expect it to?

Thanks,

Mike.

Worksheet_Calculate Running on Every Sheet
 

I'm sorry, I should have been more specific. I do indeed have Indirect in my
worksheets, as well as references to the worksheet that contains my
Worksheet_Calculate function, which would cause the sheet with my function in
it to be recalculated, triggering the function in that sheet. That's not the
issue though.

..... I was just about to explain how I was seeing the Worksheet_Calculate
function acting upon every sheet in the file, when it finally dawned upon me
what was going on. The function colors cells, and it does so through
commands similar to:

cell = Intersect(Columns("M"), Rows(2))
cell.EntireRow.Interior.ColorIndex = 3

From my limited understanding of these things, I'm guessing that I'm always
coloring the active sheet. So when Sheet1 is calculated, it calls my
function, which colors whatever sheet I'm looking at.

Although that doesn't explain why my function gets called when I'm working in
a different file, which has no connection to the file that contains the
function. Suppose my function is in Sheet1 of Workbook1. I then open
Workbook2 and make a change. My function gets called, and fails because
certain things are missing from Workbook2. I can't reproduce this now though.
....

The other thing that has been happening is, everything is fine one day, I
open the file the next day and it is corrupt. It says something like
"automation error, catastrophic failure". In the VB editor I see a bunch of
'phantom' sheets listed in addition to the ones that are really there. I
rebuilt the file from scratch the first time this happened, and the next day
it happened again. I'm rebuilding it again, but I have to wonder what's
going to happen tomorrow...

Worksheet_Calculate Running on Every Sheet
 

Calculation is a little tricky to get your head around some times. When you
make changes to a cell any formulas which reference that cell are tagged as
dirty. Any functions that are volatile are always tagged as dirty. When a
calculation is initiated in any workbook, Excel goes through all of the open
workbooks looking for cells which are tagged as dirty and it updates those.
When the calculation is complete all of the dirty flags are removed and all
is well in the world. So your volatile function will always be triggered
whenever any calculation is executed in any open workbook...

You are correct in that your code will be executed against the active sheet.
What you have typed is equivalent to

cell = Intersect(activesheet.Columns("M"), activesheet.Rows(2))

Try changing the code to (untested)
cell = Intersect(Me.Columns("M"), Me.Rows(2))


--
HTH...

Jim Thomlinson


"mkelly4ca" wrote:

I'm sorry, I should have been more specific. I do indeed have Indirect in my
worksheets, as well as references to the worksheet that contains my
Worksheet_Calculate function, which would cause the sheet with my function in
it to be recalculated, triggering the function in that sheet. That's not the
issue though.

..... I was just about to explain how I was seeing the Worksheet_Calculate
function acting upon every sheet in the file, when it finally dawned upon me
what was going on. The function colors cells, and it does so through
commands similar to:

cell = Intersect(Columns("M"), Rows(2))
cell.EntireRow.Interior.ColorIndex = 3

From my limited understanding of these things, I'm guessing that I'm always
coloring the active sheet. So when Sheet1 is calculated, it calls my
function, which colors whatever sheet I'm looking at.

Although that doesn't explain why my function gets called when I'm working in
a different file, which has no connection to the file that contains the
function. Suppose my function is in Sheet1 of Workbook1. I then open
Workbook2 and make a change. My function gets called, and fails because
certain things are missing from Workbook2. I can't reproduce this now though.
....

The other thing that has been happening is, everything is fine one day, I
open the file the next day and it is corrupt. It says something like
"automation error, catastrophic failure". In the VB editor I see a bunch of
'phantom' sheets listed in addition to the ones that are really there. I
rebuilt the file from scratch the first time this happened, and the next day
it happened again. I'm rebuilding it again, but I have to wonder what's
going to happen tomorrow...

Worksheet_Calculate Running on Every Sheet
 

Thank you - that gives me what I was missing. I now have:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh Is Me.ActiveSheet Then
ColorRows Sh
End If
End Sub

And in ColorRows I've changed things such as Columns("M") to Sh.Columns("M").
Seems to work like I would expect, so far.

I'm not convinced that Excel needs to recalculate one file when another,
completely unrelated file is changed, but at least now I understand what's
going on.

Jim Thomlinson wrote:
When a
calculation is initiated in any workbook, Excel goes through all of the open
workbooks looking for cells which are tagged as dirty and it updates those.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1