Manual/automatic recalculation

  • Thread starter Thread starter rt0029py
  • Start date Start date
R

rt0029py

I have a workbook with three sheets 1, 2 and 3. Sheet 2 and 3 have formulas
that referencian sheet 1. The initial idea is that reclaculation is
automatic. The problem is that sheet 2 is simple but the 3 is enormously
complicated and automatic recalculation slows down much the handling of the
workbook. The solution that I need is that the recalculation of the sheet 2
formulas are automatic, according we are introducing data in sheet 1, but
the recalculation of the sheet 3 should be manual when pressing a button or
when updating the sheet... I would like to know if there is a way to do
this.
Thank you very much. -
 
You could turn calculation to manual, but have event code for sheet 1 and 2
that triggers sheet calculation on any change. Something like this

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
Me.Calculate

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Put this code behind sheet1 and sheet 2. Thinking about it, you could
probably put it behind Sheet3 as well, and force recalculation there by just
editing a cell, any cell. Then you could make it a workbook event not
replicated worksheet event.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
You can turn off calculation for just that sheet if you wish

Sub SheetCalc()

Dim m As String
Dim Sht As Worksheet

Set Sht = Sheets("Sheet 3")

Sht.EnableCalculation = Not Sht1.EnableCalculation

If Sht.EnableCalculation = True Then
m = "ON"
Application.CalculateFull
Else: m = "OFF"
End If

MsgBox "You have turned Calculation Status <" & m & "> for the following
sheets:-" _
& vbCrLf & vbCrLf & Sht.Name

End Sub
 
Back
Top