EnableCalculation on / off when a sheet is hidden

[Dman333]

I’m pretty new to vba and am have a workbook with 40 tabs that have a lot of calculations. I created a couple of summary sheet for the 40 tabs but it takes a while to calc. I tried to use the code below in the individual worksheets that I want to hide, but got an error.

Private Sub Calc() 'Turns calculation off if hidden
If Worksheets("Sheet61").Visible = False Then
EnableCalculation = False
End If
End Sub

It highlighted the If statement. I’d also like to confirm I’m putting it in the correct place which is the code of the individual worksheets. Lastly, I want to make sure the EnableCalculation would be off if the file is opened and the sheets are hidden. I think it may have to recalc once when it’s opened and that’s OK.

I’m in Windows 10 and Office Pro 2016

Any help is greatly appreciated and thanks in advance.

Cheers!

 

[Logit]

.

'At beginning of your macro code :
With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With


'just before the END SUB statement
With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
End With

 

[Dman333]

Thanks a ton for taking the time to respond. I still think it's calculating unless I'm testing it the wrong way. I put a simple circular formula in the sheet I was hiding in manual calc mode. Then hid the sheet and hit F9. I still got the circular reference error which to me means it's still recalculating - unless the circular error is taboo no matter what. Below is the exact code and it's in the sheet code section under general. Any thoughts on how I messed this up?

Private Sub Calc() 'Turns calculation off if hidden


With Application
.ScreenUpdating = False
.DisplayStatusBar = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With


If Worksheets("Sheet92").Visible = False Then
EnableCalculation = False
End If


With Application
.ScreenUpdating = True
.DisplayStatusBar = True
.Calculation = xlCalculationManual
.EnableEvents = True
End With


End Sub

Thanks again - Cheers

 

[Dman333]

I think I see the problem here but still not 100% sure of the solution. Apparently I made the rookie mistake of not telling you the workbook runs in manual calc mode and I use F9. I was hoping there was a way to stop it when an F9 is hit and it's hidden. Also, the sheets might be hidden when the file is opened.

Any ideas out there?

Thanks a ton!

Cheers

 

[Jaafar Tribak]

I think I see the problem here but still not 100% sure of the solution. Apparently I made the rookie mistake of not telling you the workbook runs in manual calc mode and I use F9. I was hoping there was a way to stop it when an F9 is hit and it's hidden. Also, the sheets might be hidden when the file is opened.

Any ideas out there?

Thanks a ton!

Cheers

Unless I misaunderstood you, you culd either hook the F9 key using the Application Onkey Method or assign the calculation to a button so you can decide what to do.

 

[Dman333]

Unless I misaunderstood you, you culd either hook the F9 key using the Application Onkey Method or assign the calculation to a button so you can decide what to do.

Interesing idea, but the file is used by a large team. So, I'm looking for something that works in the background so other users don't need training.

Thanks for the idea,

Cheers!

 

[Dman333]

Unless I misaunderstood you, you culd either hook the F9 key using the Application Onkey Method or assign the calculation to a button so you can decide what to do.

How do you do that? I've never "hooked" a key. Does it just apply to the key meaning if they use the menu to recalc it will recalc the hidden sheets?

Is there code that will execute whenever there's a recalc whether it's manual or automatice. If so, then we could put the code you suggested inside that can't we?

You can tell I don't do this very much so thanks for your time.

Cheers!

 

[Jaafar Tribak]

Hi Dman333,

Your requirement is not quite clear to me ... Can I ask you to rephrase what the problem is and clearly state what you are after.

I will be logging off shortly so I'll reply later on.

 

[Dman333]

Sure -Thanks.

I want to hide sheets and the sheets may be hidden when others open it. They are slowing down performance so I want to hide them and have calculations turned off when they're hidden. Once they're visible, I want the calculation to automatically turn on again. I need this to happen whether the workbook is in automatic or manual calculation mode. The goal is to speed up calculation time by hiding the sheets. When you mentioned "hook the F9 key using the Application Onkey Method or assign the calculation to a button so you can decide what to do
in your post. But that sounds like users who I've never met would have to know what to do. I could send the sheet out in manual calc mode and they may turn it back on and therefore never need to hit a button. Or. they may use the menu "calculate now" item in formulas on the menu. Like I said, I'm a novice at this, but it sounded like your suggestion would only work if they hit F9 or a control button.

So I'm looking for a way in the background to control calculation of the hidden sheets that is transparent to a user and doesn't impact their options for calculation.

Thanks a ton for taking the time to even address my little issue. It's truly appreciated.

Cheers!

 

[Jaafar Tribak]

How are you (and users) hiding and unhiding the sheets ?