Auto Calculation Toolbar Button

[babsc01]

Hey everyone...my next attempt at coding something useful for myself is to try creating a Toolbar button that will set workbook calculation (TOOLS-OPTIONS-CALCULATION) to automatic or manual. I'm trying to avoid going into OPTIONS every time I want to turn Calculation on or off.

Has anyone given this a go yet? I would appreciate any feedback.

[HalfAce]

Hi,
Don't know if you need help creating the toolbar button or not, but for the code you can use:

Sub ToggleAutoManualCalc()
With Application
    If Not .Calculation = xlManual Then .Calculation = xlManual 
    Else: .Calculation = xlAutomatic
    End With
End Sub

This any help?
Dan

[mdmackillop]

Hi BabSc,
Create a new toolbar called "Calcs" with one button on it. Assign it to the following macro. You can swap things around of course.
MD

Sub ManAuto()
With Application
    If .Calculation = xlCalculationManual Then
        .Calculation = xlCalculationAutomatic
        Set myControl1 = CommandBars("Calcs").Controls(1)
        myControl1.State = msoButtonUp
    Else
        .Calculation = xlCalculationManual
        Set myControl1 = CommandBars("Calcs").Controls(1)
        myControl1.State = msoButtonDown
    End If
End With
End Sub

[Zack Barresse]

To assign to a custom button...

Right click an empty space around your toolbars, click Customize. Click the Commands tab and in the left pane, scroll down to Macros. Click and drag the smiley face in the right pane over to your toolbar (wherever you want it to go), then close the Customize window. When you click on your smiley face button for the first time, it will as you what macro you want to assign to it. Given you've put one of the above routines in a macro, type in the path or select it from the list and click OK. You should be set! So everytime you click that button (w/ the above code(s)), you should be toggling the Calculation mode.

[babsc01]

Got a "run time error 5...invalid procedure call or argument" on this line:

Else
    .Calculation = xlCalculationManual
                Set myControl1 = CommandBars("Calcs").Controls(1)
    myControl1.State = msoButtonDown

I'm trying to debug...any thoughts?

[babsc01]

Never mind all...it is working BEAUTIFULLY now (my dumb mistake). Thanks again MD and Zack!. Hopefully I'll be able to post something to help out soon!!

[Richie(UK)]

Another one to consider ...

Sub CalcMode()
    Static iCalcMode As Integer
    Dim iMode As Integer, strText As String
iCalcMode = iCalcMode Mod 3 + 1
    'cycle through the references
Select Case iCalcMode
    Case 1: iMode = xlCalculationAutomatic: strText = "Auto"
    Case 2: iMode = xlCalculationManual: strText = "Manual"
    Case 3: iMode = xlCalculationSemiautomatic: strText = "Semi-auto"
    End Select
    'determine mode to apply
Application.Calculation = iMode
    MsgBox strText
    'or whatever indicator you want
End Sub

[babsc01]

Looks like Richie's will work great too.

Another thread SOLVED! What a Friday!