Macro to hide/show & enable AutoCalc on certain sheets

[RPM7]

Hey guys,

I have an usual vba question to ask.
Is it possible to create a worksheet that acts as a directory for all the worksheets in workbook?
I want to be able to use this directory to show/hide certain worksheets & also enable/disable autocalc for certain worksheets as well.
I've done a lot of searching and I'm struggling to find anything that comes close to achieving this.

I have a worksheet named "Directory" and all of my worksheets are listed from A2:A39.
Column D is used to show worksheets with a blank cell for "Yes"
Column E is for enabling Auto calc for the respective worksheet with "Yes" in the column cell.

I'm hoping that I can hide/unhide by typing "Yes" in col D and similarly enable/disable the auto calc on the sheets by typing "Yes" in col E.

Any help would be greatly appreciated.

 

[James006]

Hello,

If all your worksheets are part of the same workbook ... which seem to be the case ...

1. The Index worksheet listing all your worksheets can handle the Show / Hide feature you need for each sheet ...

2. Depending on what you are calling ' AutoCalc ' ... you are dealing with a feature at the Excel Options level : manual or automatic calculation ...

Hope this will help

 

[RPM7]

Hi James, thanks for your reply.

1) I've already created an index list. Probably not the most elegant way though.
I have a macro that runs through a list of names and renames the tabs in bulk.
I had to edit each cell after the name change, as the links weren't updating automatically.

<a href=https://drive.google.com/open?id=1B5Eg9D2bKI34diknurju-CAR3nbxNV0X>Here's a link to download the spreadsheet</a>

2) Exactly, I want to be able to switch on/off the manual calculation. The spreadsheet is pretty big and is slowed down a lot by some sheets.
The example spreadsheet I've linked above shows what I'm trying to achieve.

Thanks

 

[RPM7]

For anyone interested, I've got the worksheets to hide / unhide as driven by a cell value in my "Directory" worksheet.
Where I have the Yes option for hiding / unhiding the tabs, I've renamed each cell "Show_Tab_#".

Private Sub Worksheet_Change(ByVal Target As Range)

If [Show_Tab_2] = "Yes" Then
Sheet2.Visible = True
Else
Sheet2.Visible = False
End If 

If [Show_Tab_3] = "Yes" Then
Sheet3.Visible = True
Else
Sheet3.Visible = False
End If

End Sub

I'm still struggling with turning the automatic calculation on/off.

If anyone can help out there, I'd really appreciate it.

 

[James006]

Hi,

Below are two macros to turn On and Off the Calculation ...

Sub TurnOffCalc()
  Application.Calculation = xlCalculationManual
End Sub


Sub TurnOnCalc()
  Application.Calculation = xlCalculationAutomatic
End Sub

Hope this will help

 

[RPM7]

Thanks James.

Do I add that code to each worksheet and call them from a workbook module?

Worksheet:

Sub TurnOffCalc_Tab10()
  Application.Calculation = xlCalculationManual
End Sub


Sub TurnOnCalc_Tab10()
  Application.Calculation = xlCalculationAutomatic
End Sub

Workbook:

Sub Calc()
If [Calc_Tab10] = "Yes" Then
call TurnOnCalc_Tab10()
Else
call TurnOffCalc_Tab10()
End If

End Sub

 

[James006]

Hello,

In my opinion, it would be much easier to selectively use :

Private Sub Worksheet_Activate()
  Application.Run ("TurnOffCalc")
End Sub


Private Sub Worksheet_Deactivate()
  Application.Run ("TurnOnCalc")
End Sub

Hope this will help