Help VBA Me.Calculate

[MartinL]

OK so here is the scenario

smart alec me decides the auto calc function makes the workbook way to slow

so I make the Workbook manual on startup
Add this to the from of my code

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

some code here

me.calculate

a bit more code

Application.EnableEvents = True 
Application.ScreenUpdating = True 
Application.DisplayAlerts = True

but it seems that the me.calculate does not work whilst the application.x = False

is there a way around or am I missing something

Martin

 

[AlphaFrog]

It depends on what Me is (where your code is located)...

Calculate Method

 

[MartinL]

Hi AlphaFrog

Ok, I use

Sheets("Data").Select

just prior to the me.calculate

when I step through the code using F8 it works
but not when left to run

I will try some of the other methods in your link as I only need to calculate 5 out of the 20 columns

Am I wrong in thinking ME is the active sheet at time of reaching line of code

Martin

 

[AlphaFrog]

Am I wrong in thinking ME is the active sheet at time of reaching line of code

The Me keyword is not the same as ActiveSheet

The Me Keyword

 

[GTO]

...Am I wrong in thinking ME is the active sheet at time of reaching line of code

Martin

I am presuming that Me.Calculate is in a worksheet's module, as I don't believe that calculate will aim at a specific workbook. If that is the case, Me.Calculate should calculate the worksheet that the code resides in, regardless of what sheet is active. In short, Me is aimed at the object it resides in, like ThisWorkbook refers to the workbook it resides in.

Hope that helps,

Mark

 

[MartinL]

Ahhh

OK I get it,

I have just been fortunate that all my previous work has followed this rule:

this time I just got bit unfortunate (

this was my code, just so someone else does not make the same mistake..

WRONG USE OF ME Keyword

Private Sub CommandButton1_Click()

Sheets("EPOS1").Activate
Me.Calculate

Sheets("EPOS2").Activate
Me.Calculate

Sheets("Pivots (£)").Activate
Sheets("Pivots (u)").Activate
Sheets("Pivots (w)").Activate

Sheets("Market.Share (3)").Activate
Me.Calculate

Sheets("Market.Share (2)").Activate
Me.Calculate

Sheets("Market.Share").Activate
Me.Calculate

End Sub

Changed to this, no need to activate the sheet

Private Sub CommandButton1_Click()

Sheets("EPOS1").Calculate
Sheets("EPOS2").Calculate

 
     'Pivot sheets have On Activation code to set up all the PAGEFIELDS
Sheets("Pivots (£)").Activate
Sheets("Pivots (u)").Activate
Sheets("Pivots (w)").Activate

Sheets("Market.Share").Calculate
Sheets("Market.Share (2)").Calculate
Sheets("Market.Share (3)").Calculate

Sheets("Market.Share").Activate
End Sub

Thanks for your help guys, especially for the links

Martin