Consulting

Results 1 to 8 of 8

Thread: Solved: Auto Calculation Toolbar Button

  1. #1
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location

    Lightbulb Solved: Auto Calculation Toolbar Button

    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.

  2. #2
    VBAX Regular
    Joined
    Jun 2004
    Location
    Cordova, Alaska
    Posts
    10
    Location

    Toggle Auto/ Manual Calculation

    Hi,
    Don't know if you need help creating the toolbar button or not, but for the code you can use:
    [VBA]Sub ToggleAutoManualCalc()
    With Application
    If Not .Calculation = xlManual Then .Calculation = xlManual Else: .Calculation = xlAutomatic
    End With
    End Sub[/VBA]This any help?

    Dan

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Automatic or Manual Calculation button

    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

    [VBA]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
    [/VBA]

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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.

  5. #5
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    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?

  6. #6
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    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!!

  7. #7
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Another one to consider ... [vba]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[/vba]

  8. #8
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    Looks like Richie's will work great too.

    Another thread SOLVED! What a Friday!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •