Can I completely disable a worksheet without deleting it?

[cjcass]

Hi,

I have an issue that's doing my head in, can anyone help??!!

I've got a large workbook in Excel 2010 that has about 50 worksheets, 100 macros and is about 80MB in size. There are lots of formulas, change events and macros that all run in a strict chronological order to feed lots of metrics and graphics. I press a button which calls the macros in a sequential order and populates the whole workbook at great speed (usually in about 45secs). However it now runs slow (5mins). Through a long process of elimination I have found the problem but not a solution.

There are two worksheets which when deleted from the workbook solve the issue and the macros fly again, however I need to retain the worksheets in the workbook. On scouring the worksheets there appears to be nothing untoward within them - they are not reading 'live' from a source that is being updated by the other macros etc.

Is there a way of completely isolating these two worksheets when the macros are running or by making them temporarily invisible/non-active??

I have tried the following code in each sheet but it has no effect:

Option Explicit
  
Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    
End Sub
 
Private Sub Worksheet_Deactivate()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
End Sub

Any help massively appreciated!!
Thanks,

 

[gallen]

What is in these sheets? Just text? Make sure they aren't full of conditional formatting which can really slow things down.

 

[RoryA]

BTW it's generally not a good idea to disable events at the end of an event code because no other events will run subsequently to turn them back on.

 

[cjcass]

All worksheets contains lots of conditional formatting, array formulas, shapes with formatting driven by change events, graphs, tables etc. etc. All of which are no problem when these two sheets are removed so it's not that.
My target is a method to properly isolate the two sheets.

 

[cjcass]

Take your point re. the change event code - what I had input doesn't work so I'm guessing it's no good anyhoo

 

[RoryA]

Really, you'd need to figure out precisely what it is about those sheets that causes the issue, but you could try a routine like this:

Sub EnableSheet(ws As Worksheet, Optional bEnable As Boolean = True)
    With ws
        .EnableCalculation = bEnable
        .EnableFormatConditionsCalculation = bEnable
    End With
End Sub

You can then disable calculation and conditional formatting for those sheets like this:

EnableSheet sheets("sheet name"), False

and then re-enable at the end:

EnableSheet sheets("sheet name")

 

[cjcass]

Hi, thanks for the info.
Just so I get this right - where should I input the code(s)?
Thanks,

 

[RoryA]

The first code needs just be in a normal module somewhere. The other two codes need to go at the start and end of the routines that are getting bogged down by the two sheets. No guarantee they will work as we don't know the cause of the slowdown for sure.

 

[cjcass]

Hi,
I tried that but it didn't seem to work. I inserted a module with the first code and input the other two bits of code at the beginning and end of the routines but no luck, any thoughts?

 

[RoryA]

Not without the workbook. Until you know why they cause a problem, it's hard to fix other than by just throwing things at the problem in the hope they work.