I have workbook with lots of sheets and formulas and it takes forever to
recalc the workbook, whenever a new entry is made in a cell. Is it possible
to disable recalc for the whole workbook, and then start it with a
controlbutton?

Vista Ultimate/Office 2007
--
My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Hi,

You can disable manually or with code

Sub Yoursub()
Application.Calculation = xlCalculationManual
'your code
Application.Calculation=xlCalculationAutomatic

or
Office button - Excel Options - Fromulas and set to manual or automatic

Or attach the code above to a button on the Quick Access Toolbar (QAT) or on
the worksheet

Mike

"Kjeldc" wrote:

> I have workbook with lots of sheets and formulas and it takes forever to
> recalc the workbook, whenever a new entry is made in a cell. Is it possible
> to disable recalc for the whole workbook, and then start it with a
> controlbutton?
>
> Vista Ultimate/Office 2007
> --
> My programming is self-taught and
> my teacher was not very experienced. :-)
>
> cheers,
> Kjeld

If you want your button to act like a toggle, then just assign this macro to
it:

Sub CalcTog()

If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If

End Sub

"Kjeldc" wrote:

> I have workbook with lots of sheets and formulas and it takes forever to
> recalc the workbook, whenever a new entry is made in a cell. Is it possible
> to disable recalc for the whole workbook, and then start it with a
> controlbutton?
>
> Vista Ultimate/Office 2007
> --
> My programming is self-taught and
> my teacher was not very experienced. :-)
>
> cheers,
> Kjeld

Here is another way to write your toggle macro (a one-liner)...

Sub CalcToggle()
Application.Calculation = xlCalculationManual + xlCalculationAutomatic - Application.Calculation
End Sub

--
Rick (MVP - Excel)


"B Lynn B" <(E-Mail Removed)> wrote in message news:09C35F97-8B67-4D1E-890E-(E-Mail Removed)...
> If you want your button to act like a toggle, then just assign this macro to
> it:
>
> Sub CalcTog()
>
> If Application.Calculation = xlCalculationAutomatic Then
> Application.Calculation = xlCalculationManual
> Else
> Application.Calculation = xlCalculationAutomatic
> End If
>
> End Sub
>
> "Kjeldc" wrote:
>
>> I have workbook with lots of sheets and formulas and it takes forever to
>> recalc the workbook, whenever a new entry is made in a cell. Is it possible
>> to disable recalc for the whole workbook, and then start it with a
>> controlbutton?
>>
>> Vista Ultimate/Office 2007
>> --
>> My programming is self-taught and
>> my teacher was not very experienced. :-)
>>
>> cheers,
>> Kjeld

Thanks a'lot :-)
--
My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Mike H" skrev:

> Hi,
>
> You can disable manually or with code
>
> Sub Yoursub()
> Application.Calculation = xlCalculationManual
> 'your code
> Application.Calculation=xlCalculationAutomatic
>
> or
> Office button - Excel Options - Fromulas and set to manual or automatic
>
> Or attach the code above to a button on the Quick Access Toolbar (QAT) or on
> the worksheet
>
> Mike
>
> "Kjeldc" wrote:
>
> > I have workbook with lots of sheets and formulas and it takes forever to
> > recalc the workbook, whenever a new entry is made in a cell. Is it possible
> > to disable recalc for the whole workbook, and then start it with a
> > controlbutton?
> >
> > Vista Ultimate/Office 2007
> > --
> > My programming is self-taught and
> > my teacher was not very experienced. :-)
> >
> > cheers,
> > Kjeld