Can I completely disable a worksheet without deleting it?

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
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:
Code:
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,
 
Last edited by a moderator:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
What is in these sheets? Just text? Make sure they aren't full of conditional formatting which can really slow things down.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:
Code:
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:
Code:
EnableSheet sheets("sheet name"), False
and then re-enable at the end:
Code:
EnableSheet sheets("sheet name")
 

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Last edited:

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,529
Messages
5,659,343
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top