I'm stepping through a Standard module on my Sheet2, but suddenly my code
jumps to a Private Sub Worksheet_Calculate which is a part of my Worksheet7.
Why is that?

If you changed a cell value that would trigger a recalc, then that happens
all the time.

It is best to preceed your code with this is you make and changes to cell in
code:

Dim myCalc As Excel.XlCalculation
With Application
.EnableEvents = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'and finish with
With Application
.EnableEvents = True
.Calculation = myCalc 'xlCalculationAutomatic is the usual setting
.ScreenUpdating = True
End With

HTH,
Bernie
MS Excel MVP

"JMay" <(E-Mail Removed)> wrote in message
news:A8203E4D-5595-4434-BEA1-(E-Mail Removed)...
> I'm stepping through a Standard module on my Sheet2, but suddenly my code
> jumps to a Private Sub Worksheet_Calculate which is a part of my
> Worksheet7.
> Why is that?

E-X-P-A-N-D-I-N-G on your comment
"If you changed a cell value that would trigger a recalc, then that happens
all the time" -

might I embellish your statement by saying:

If you change a cell value ON ANY WORKSHEET - that triggers a recalc, AND
IF you have a Worksheet_Calculate on ANY Code-Sheet in your ActiveBook, This
Event Code Module Fires - and that happens all the time.

Right?????

Thanks, Jim

"Bernie Deitrick" wrote:


> If you changed a cell value that would trigger a recalc, then that happens
> all the time.
>
> It is best to preceed your code with this is you make and changes to cell in
> code:
>
> Dim myCalc As Excel.XlCalculation
> With Application
> .EnableEvents = False
> myCalc = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> 'and finish with
> With Application
> .EnableEvents = True
> .Calculation = myCalc 'xlCalculationAutomatic is the usual setting
> .ScreenUpdating = True
> End With
>
> HTH,
> Bernie
> MS Excel MVP
>
> "JMay" <(E-Mail Removed)> wrote in message
> news:A8203E4D-5595-4434-BEA1-(E-Mail Removed)...
> > I'm stepping through a Standard module on my Sheet2, but suddenly my code
> > jumps to a Private Sub Worksheet_Calculate which is a part of my
> > Worksheet7.
> > Why is that?
>
>

Nope.

Try a small experiment.

Create a new workbook with 3 sheets.

Put this behind each worksheet:

Option Explicit
Private Sub Worksheet_Calculate()
MsgBox Me.Name
End Sub


Then go back to one sheet and type this in any cell:
=rand()
and hit enter.

Excel is pretty smart when it thinks, er, knows that it has to recalculate a
sheet.

JMay wrote:
>
> E-X-P-A-N-D-I-N-G on your comment
> "If you changed a cell value that would trigger a recalc, then that happens
> all the time" -
>
> might I embellish your statement by saying:
>
> If you change a cell value ON ANY WORKSHEET - that triggers a recalc, AND
> IF you have a Worksheet_Calculate on ANY Code-Sheet in your ActiveBook, This
> Event Code Module Fires - and that happens all the time.
>
> Right?????
>
> Thanks, Jim
>
> "Bernie Deitrick" wrote:
>
> > If you changed a cell value that would trigger a recalc, then that happens
> > all the time.
> >
> > It is best to preceed your code with this is you make and changes to cell in
> > code:
> >
> > Dim myCalc As Excel.XlCalculation
> > With Application
> > .EnableEvents = False
> > myCalc = .Calculation
> > .Calculation = xlCalculationManual
> > .ScreenUpdating = False
> > End With
> >
> > 'and finish with
> > With Application
> > .EnableEvents = True
> > .Calculation = myCalc 'xlCalculationAutomatic is the usual setting
> > .ScreenUpdating = True
> > End With
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "JMay" <(E-Mail Removed)> wrote in message
> > news:A8203E4D-5595-4434-BEA1-(E-Mail Removed)...
> > > I'm stepping through a Standard module on my Sheet2, but suddenly my code
> > > jumps to a Private Sub Worksheet_Calculate which is a part of my
> > > Worksheet7.
> > > Why is that?
> >
> >

--

Dave Peterson

I know it has been a while since this was posted, but I had the same problem
as you were describing (WS_Calculate is occurring even when a change is made
on another WS that should not trigger the event) and after searching I found
the following response from Jim Thomlinson which helped me out greatly. As
Jim describes, I had "volatile" functions on the sheet with the WS_Change
event and these volatile functions would calculate anytime a change was made
on the sheet or on any other sheet. Try an experiment similar to the one
Dave Peterson mentioned in this post and you will see that a =Now() or
=Today() function will calculate when any change on any sheet is made.
Hopefully it is helpful to someone else with the same problem.

Copied message as follows (from 8/30/2005)...

---------------------------------------------------------------------------------------

=Now() is a volatile function, meaning that it calculates every time that a
calculation is executed. If you can, try to find a way to replace the now
function with a constant. You can update the constant base on some other
event such as a sheet activate or workbook open or... Otherwise you can also
add a criteria to the calculation event that ThisWorkbook is the active
workbook. If it isn't then abort the rest of the procedure.
--
HTH...

Jim Thomlinson


"Alex" wrote:

> I'm usinin the following code to highlight the filtered column with a
> different collar.
> To make it works I've entered =Now() in some cell.
> Everything is fine. But, when I'm opening another spreadsheet the procedure
> is being automatically triggered producing the error "out of range".
>
> Private Sub Worksheet_Calculate()
> Dim af As AutoFilter
> Dim fFilter As Filter
> Dim iFilterCount As Integer
> 'Application.EnableEvents = False
> If Worksheets("Initiatives").AutoFilterMode Then ' gettin an error "out of
> range"
> Set af = Worksheets("Initiatives").AutoFilter
> iFilterCount = 1
> Worksheets("Initiatives").Unprotect ("donit")
> For Each fFilter In af.Filters
> If fFilter.On Then
> af.Range.Cells(1, iFilterCount) _
> .Interior.ColorIndex = 3 ' 15 - gray; 35 - mint green
> Else
> af.Range.Cells(1, iFilterCount) _
> .Interior.ColorIndex = 15
> End If
> iFilterCount = iFilterCount + 1
> Next fFilter
>
> Else
> Worksheets("Initiatives").Range("A1").AutoFilter
> Worksheets("Initiatives").Range("A1:H1").Interior.ColorIndex = 15
> End If
>
> How could I fix it?
>
> Thanks