Excel make Calculation MANUAL - dashboards again..... 1

[Welshbird]

Hi All,

Right, after much discussion here I finally got my dashboard working perfectly.

To do that I send the workbook with Tools> Options> Calculation> MANUAL.

Now, on the big bosses laptop this automatically resetsitself to Automatic and then blowsup in a heap.

Any suggestions please? She is the last person that I need this to happen with, so typically this is where it is happening.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Hi,

alt+F11 toggles between the VB Editor and the worksheet.

In the VB Editor, ctr+R to view the Project Explorer.

In the Project Explorer open the VBA Project for your Workbook. You will see displayed an object tree of 1) each Worksheet in your workbook and 2) ThisWorkbook, which is the Workbook Object.

Right-click the ThisWorkbook object and select View Code

In the Code Window of the ThisWorkbook object paste this code that will run each time the workbook is opened...

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
End Sub

CAVEAT: The user MUST enable macro content when opening OR have selected the option to enable all macros without confirmation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Wow. Speedy!

I just did that, sent it to her - watched her open it with macro's enabled, and it didn't work....

Somemore investigation me thinks...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Actually - Is this because she has another workbook open first?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

No,

Paste this one in instead...

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual

    MsgBox "MANUAL"
End Sub

SAVE & CLOSE the workbook

OPEN the workbook

ENABLE macros

OBSERVE the Message Box

CONFIRM that the calculation is MANUAL in practice.

Once this excersize is complete, you can DELETE the annoying

    MsgBox "MANUAL"

statement



If you do NOT observe the MANUAL message, either your code is NOT in the ThisWorkbook object code window or your macros are not enabled.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Thanks Skip => Off to test again.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Skippy - You are magic innit.

Twinkly things in your direction.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

...and the solution was????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}