Turn off calculate event
 

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

Turn off calculate event
 

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

nobbyknownowt wrote:

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

--
nobbyknownowt

--

Dave Peterson

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

Turn off calculate event
 

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.



nobbyknownowt wrote:

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

--
nobbyknownowt

--

Dave Peterson

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