• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Disable Worksheet calculation

kuldeepjainesl

kuldeepjainesl

Member
Hi Experts,


This is a condition arisen as two sheets in my workbook are taking 5-10 min to calculate.


I need a way so that I can calculate these by F9 while keep other sheets in auto calculation mode so that my other interactive feature and chart can work based on the data calculated on these two sheets.


I tried this code in both sheet but could not get success.

[pre]
Code: 
Private Sub Worksheet_Activate()
Application.Calculation = xlManual
End Sub

Private Sub Worksheet_Deactivate()
Application.Calculation = xlManual
End Sub
[/pre]

Regards,
 
In the VBE, select the sheet, then look at the Properties dialogue (if not visible, go to View - Properties). You'll see an option called "Enable Calculation", which you can set to False. Alternatively, the macro to do this would be:

[pre]
Code: 
Sub ToggleCalculation()
Worksheets("Sheet1").EnableCalculation = _
Not (Worksheets("Sheet1").EnableCalculation)
End Sub
[/pre]
However, you can't use F9 to force a recalc. When they're turned off, they are completely off. You can't use F9, you can't enter and re-confirm the cell. However, one you re-enable the calculation, the sheet will recalculate at that point. Will this work?
 
Yup thanks.


A bit information to remember every time to run the macro and press f9 to calculate and again run the macro to disable calculation.


Is it possible to check this property and change a value in predefined cell so that i can use this as a instrument to let the user know the status of auto calculations?


Regards,
 
You'd only have to press F9 if the rest of the workbook is in manual calculation mode.

You could certainly add a line to the macro to add a flag to a cell.

[pre]
Code: 
Sub ToggleCalculation()
With Worksheets("Sheet1")
.EnableCalculation = Not (.EnableCalculation)
.Range("A2") = .EnableCalculation 'Add flag
End With
End Sub
[/pre]
 
Aaah...It seems that this setting is over written when the workbook is closed. We need to open workbook with these calculation disable.


I hope we need a code to execute on opening and closing the workbook to disable calculation for 2-3 predefined sheets.


I am using this peice of code.


is it OK?

------------------------------------------------

[pre]
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Unique Lists")
.EnableCalculation = False
.Range("Flag_2") = .EnableCalculation 'Add flag
End With
With Worksheets("Data Processing")
.EnableCalculation = False
.Range("Flag_1") = .EnableCalculation 'Add flag
End With
End Sub
-----------------------------and-----------------------------

Private Sub Workbook_Open()

With Worksheets("Unique Lists")
.EnableCalculation = False
.Range("Flag_2") = .EnableCalculation 'Add flag
End With
With Worksheets("Data Processing")
.EnableCalculation = False
.Range("Flag_1") = .EnableCalculation 'Add flag
End With
End Sub
[/pre]
Edit: Opps, this also did not work and started calculation and needed to press "ESC"


Regards,
 
Hmm. Might be able to prevent this by going to the specific sheet modules and adding something like this:

[pre]
Code: 
Private Sub Worksheet_Calculate()
Me.EnableCalculation = Range("Flag_1")
End Sub
[/pre]
That should make it so that they only calculate if the Flag is true.
 
No Luck Luke,


But I think I have an workaround/idea/trick and need a expert advice/help.


It seems to me that if Excel opens in manual mode everything goes fine but in automatic mode it treat all the calculation to be pending by default as macros are disabled yet and by the time you enable these macros, it get in to the calculations.


So why don't we we have a macro on before close event to switch workbook in to manual calculation mode so it won't start calculation while opening and this workbook & we will enable calculation by macro on open event and now all the above solution are active will prevent these sheet to calculate.


I am using this piece of code in my worksheet for open and close event. Might be this give you a clue.

[pre]
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Unique Lists")
.EnableCalculation = False
.Range("Flag_2") = .EnableCalculation 'Add flag
End With
With Worksheets("Data Processing")
.EnableCalculation = False
.Range("Flag_1") = .EnableCalculation 'Add flag
End With

'Step 1:  Declare your variables
Dim ws As Worksheet
'Step 2: Unhide the Starting Sheet
Sheets("Start").Visible = xlSheetVisible

'Step 3: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets

'Step 4: Check each worksheet name
If ws.Name <> "Start" Then

'Step 5: Hide the sheet
ws.Visible = xlVeryHidden
End If

'Step 6:  Loop to next worksheet
Next ws
End Sub

Private Sub Workbook_Open()

With Worksheets("Unique Lists")
.EnableCalculation = False
.Range("Flag_2") = .EnableCalculation 'Add flag
End With
With Worksheets("Data Processing")
.EnableCalculation = False
.Range("Flag_1") = .EnableCalculation 'Add flag
End With

'Step 1:  Declare your variables
Dim ws As Worksheet

'Step 2: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets

'Step 3: Unhide All Worksheets
ws.Visible = xlSheetVisible

'Step 4:  Loop to next worksheet
Next ws

'Step 5:  Hide the Start Sheet
Sheets("Start").Visible = xlVeryHidden
Sheets("Supply data").Visible = xlVeryHidden
Sheets("Failure data").Visible = xlVeryHidden

End Sub
[/pre]

Regards,
 
Code looks good, but I may have some bad news. =(


While it's true that we can save a workbook in manual calculation mode, and if we have nothing else open, it will re-open in manual mode. However, it you already have XL open and the current workbook is in automatic mode, when you open your workbook it will "inherit" the calculation settings and be switched to automatic.
 
You must log in or register to reply here.
Back
Top