Prev: Forumla for %, i.e. 4 over 0 should equal 400% but get #DIV/0!
Next: shifting cells disabled.
From: BJ&theBear on 26 Apr 2010 07:26
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
From: BJ&theBear on 26 Apr 2010 07:27
On Apr 26, 12:26 pm, "BJ&theBear" <brian.jerm...(a)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.
>
>  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
From: Mike H on 26 Apr 2010 08:10
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
> .
>
From: BJ&theBear on 26 Apr 2010 08:54
On Apr 26, 1:10 pm, Mike H <Mi...(a)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 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
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks Mike

Much appreciated

BJ
From: Charles Williams on 26 Apr 2010 09:37
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
 | 
Pages: 1
Prev: Forumla for %, i.e. 4 over 0 should equal 400% but get #DIV/0!
Next: shifting cells disabled.