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

On Apr 26, 12:26=A0pm, "BJ&theBear" <brian.jerm...@googlemail.com>
wrote:
> 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.
>
> =A0I 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

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" wrote:

> 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
> .
> 

On Apr 26, 1:10=A0pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> 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 activa=
tes
> to auto for all other sheets
>
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> If ActiveSheet.Name =3D ("Sheet1") Then
> =A0 =A0 Application.Calculation =3D xlManual
> Else
> =A0 =A0 Application.Calculation =3D 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" wrote:
> > 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.
>
> > =A0I 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
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks Mike

Much appreciated

BJ

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

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