View Full Version : [SOLVED:] Auto Calculation Toolbar Button

07-16-2004, 11:37 AM
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. :cool:

07-16-2004, 11:52 AM
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?

07-16-2004, 12:07 PM
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. :vv

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

Zack Barresse
07-16-2004, 12:45 PM
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.

07-16-2004, 12:54 PM
Got a "run time error 5...invalid procedure call or argument" on this line:

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

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

07-16-2004, 12:58 PM
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!!

07-16-2004, 01:00 PM
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

07-16-2004, 01:02 PM
Looks like Richie's will work great too.

Another thread SOLVED! What a Friday!