Turn off calculate event

N

nobbyknownowt

Hopefully this is an easy one for you.
I have a worksheet calculate event that works fine until I try to
clear the data on my sheet with a macro written to ensure the correct
data is deleted as the totals the calculate event uses all #N/A .
Is there code I can put into the macro at the start to disable the
calculate event and at the end to re-enable it?
cheers
nobby
 
D

Dave Peterson

You could turn off events:

application.enableevents = false
'your code
application.enableevents = true

But I would think it would be more usual to just turn calculation to manual, do
the work, and then reset it the way it was.

And there are other things that can slow down your macro, too:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub
 
N

nobbyknownowt

I had a play and went with enable events, thanks

The sheet is designed for other users and I need to keep it a simple as
possible

However I have now found a glitch

There are 4 seperate worksheets in te workbook and 4 seperate
calculate events. When I activate a worksheet and start completing it
a calculate event from another sheet sometimes stalls the program.
How do I make the calculate event sheet specific?


cheers
Nobby
 
D

Dave Peterson

Calculation is not worksheet specific. It's not even workbook specific. It's
an application setting.

So you can turn calculation to manual and recalc when you want to
(tools|Options|calculation tab|calc now.

=====
But depending on what version of excel you're running, you can actually turn
calculation off for specific worksheets.

worksheets("Sheet1").EnableCalculation = False

IIRC, this was added in xl2002.

But you'll have to turn it back on before calculating--even hitting F9 won't
help.
 
N

nobbyknownowt

Thanks for the advice Dave
I fixed it by adding an =ISNA formula to ignore the #N/A errors on each
sheet then using the "true" resuklt in an =IF to create a 0 or the
original formula result then had the calculate event point to this.
Therefore the calculate event can run all the time as when the sheet is
not in use the new result 0 will not cause an error.

Thanks again for the direction even though it was that i could not do
it the way I wanted without losing automation. At least it sent me off
in anoyjer ditrection

Nobby
 
Top