Turn on/off calculation in worksheet

[BJ&theBear]

Can anyone please advise me how I would write a worksheet selection
event code so that when you select a particular worksheet it turns the
calculation on and then turns it off when you select another worksheet
in the same workbook.

I have one particular part of a worksheet that extracts a whole load
of information from the main sheet using a series of sumproduct
formulas but of course this is very slow in doing the calculations and
as this information is only required on the odd occasion it would be
beneficial and a whole lot quicker if I was able to turn off the
calculation on that particular worksheet until it was required.

Any help would be most appreciated. -

ps I have never used a worksheet event but have been advised that this
is the best course of action - I have no idea how or where to input
this coding/information

Thanks

Bjthebear

 

[BJ&theBear]

Can anyone please advise me how I would write a worksheet selection
event code so that when you select a particular worksheet it turns the
calculation on and then turns it off when you select another worksheet
in the same workbook.

 I have one particular part of a worksheet that extracts a whole load
of information from the main sheet using a series of sumproduct
formulas but of course this is very slow in doing the calculations and
as this information is only required on the odd occasion it would be
beneficial and a whole lot quicker if I was able to turn off the
calculation on that particular worksheet until it was required.

Any help would be most appreciated. -

ps I have never used a worksheet event but have been advised that this
is the best course of action - I have no idea how or where to input
this coding/information

Thanks

Bjthebear

Sorry using Excel 2003 - forgot to mention

BJthebear

 

[Mike H]

Hi,

To instal this use ALT+F11 to open VB editor. Doubleclick 'ThisWorkbook' and
paste the code in on the right. Change the name of the sheet to suit and
whwnever that sheet is activated calculation changes to manual and activates
to auto for all other sheets

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = ("Sheet1") Then
Application.Calculation = xlManual
Else
Application.Calculation = xlAutomatic
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[BJ&theBear]

Hi,

To instal this use ALT+F11 to open VB editor. Doubleclick 'ThisWorkbook' and
paste the code in on the right. Change the name of the sheet to suit and
whwnever that sheet is activated calculation changes to manual and activates
to auto for all other sheets

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = ("Sheet1") Then
    Application.Calculation = xlManual
Else
    Application.Calculation = xlAutomatic
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.








- Show quoted text -

Thanks Mike

Much appreciated

BJ

 

[Charles Williams]

Sadly that won't work: Calculation mode is set for all the worksheets
in all the open workbooks, so when you select another sheet
calculation mode will be set to automatic and ALL the sheets will ne
calculated.

You would have to do something like this:

Set Calculation to Manual.

in the Thisworkbook module add this

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
Worksheets("SumproductSheet").EnableCalculation = False
End Sub

and in the SumProductSheet module add this

Private Sub Worksheet_Activate()
ActiveSheet.EnableCalculation = True
Calculate
ActiveSheet.EnableCalculation = False
End Sub

regards
Charles