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!

 

[Jaafar Tribak]

See if this work for you :

In the Thisworkbook module :

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.EnableCalculation = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Visible <> xlSheetVisible Then
        Sh.EnableCalculation = False
    End If
End Sub

 

[Dman333]

Could be a number of different ways. I could add a button or menu item which would make it easy. But, I can't rely on them using that exclusively. Could be a right click on one or more sheets. Or, could be via an add-in - I typically highlight the sheets and right click and then use an add-in to unhide all sheets. That's why I was looking for a way to have the code be in each sheet and be triggered when a calc is performed. Does that help?

 

[Jaafar Tribak]

Could be a number of different ways. I could add a button or menu item which would make it easy. But, I can't rely on them using that exclusively. Could be a right click on one or more sheets. Or, could be via an add-in - I typically highlight the sheets and right click and then use an add-in to unhide all sheets. That's why I was looking for a way to have the code be in each sheet and be triggered when a calc is performed. Does that help?

Have you tried the code in post#11 ?

 

[Dman333]

I'm not sure how they do it, but I usually right click a tab and hide / unhide that way. Sometimes if there's a lot of sheets I'll use an add-in called Excel Utilities that will unhide all sheets and then rehide the same ones.

Does that help?

 

[Dman333]

Hi Jaafar,

It's been a while since I've logged in and just saw this post. I actually gave up on this but will go back and see if this works. From the code I think this is exactly what I'm looking for.

Thanks a ton for taking time to respond. I didn't mean to be rude by not responding - just been a while since I've been on here.

Thanks again.