Stop Excel from evaluating formulae unless I want it to

  • Thread starter Thread starter jamie.cockrill
  • Start date Start date
J

jamie.cockrill

Hi All,

I've just had to create a monster sheet that essentially enables me to
do a SUMIF over 2 criteria, however there's some 15,000 cells involved
in this new sheet and it really slows the workbook down on startup and
every time it re-evaluates the formulae.

Is there any way that I could set just that sheet so that it doesn't
re-evaluate the formulae unless I ask it to (by means of a prompt or
any other method)?

Thanks

Jamie
 
is that a global setting for the entire workbook or will that just
apply to that sheet?

Thanks

Jamie
 
Unfortunately it's a global setting. You can manipulate it in the way you
request with a Workbook_SheetActivate event sub:

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

Regards,
Stefi
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why won't target cell data changes trigger formula evaluation? 3
formula setting cell type to blank and conditional formatting 6
SUMIF formula using data from another file 2
want sumif function's range to evaluate 2 columns 7
SUMIF, 3D FORMULA, EXCEL FORMULA HELP 1
Formula not evaluated automaticly in Office 2003 2
A formula/function to return a formula/function 4
IF OR AND in same conditional formula 2

Back
Top