Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts

    How to set Excel 2007 options radio button to Manual mode only

    Is there a way to set Excel 2007 to default to opening such that under Excel Options/Formulas/Calculation Options the radio button is set to "Manual" only, and not to "Automatic"?

    Thanks,
    Dick

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    As far as I know, Excel's calc mode is determined by the first Excel file you open in a session.
    (The calc mode is 'saved' within each file.)
    So, if the first file you open is in 'manual' mode, this sets the mode for the current Excel session.
    So if you then open another file (that was originally saved with calc mode set to 'automatic'), and then save this file, it will now be set, and saved, to 'manual mode'.

    If the first file you open is in 'automatic' calc mode, and you then open other files, whenever you save a file it will be saved with the 'current' setting of the calc mode.
    So, if during an Excel session with multiple files open, you manually change the calc mode setting, this current setting will be what is 'saved' whenever you save an excel file in that session.

    You can add a bit of vba code to a workbook open event which checks the current setting, and then changes the calc mode to what you want.

    zeddy

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Thanks Zeddy. If you or some other kind soul could list the vba code (I'm clueless how to do it myself), that would be a great help to me. Also, if I had that code, could it be added to Book1 or something else so that it would act globally through all of Excel?
    best,
    Dick

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Code:
    Private Sub Workbook_Open()
        Application.Calculation = xlManual
    End Sub
    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Dick-Y (2013-05-13)

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Dick

    Maud has given you the code to ensure that a particular workbook will always be opened with calc mode set to manual.

    But, lets be careful with what you are asking for:
    Excel is primarily a calculating engine, and to turn the calc mode to manual can be somewhat 'selfish', and very inconvenient for other users!
    For example, if you open a commonly used excel file in your Excel session where you have 'turned off' automatic calcs, and you then save a file which may be used by others, then, when that particular file is opened by another user as the first file in their session, they may not be expecting the file to be in 'manual mode'.
    I know that this has been tedious for some users who expect the files they open to be in automatic calc mode and are puzzled when they discover calc mode is 'manual' for some files when it shouldn't be.

    It is possible to include a message on a startup excel worksheet which shows the calc mode, using this excel formula:
    =INFO("RECALC")
    ..but to properly show the correct calc status, i.e. whenever calc mode is changed, requires that this cell containing this formula be 'updated' using event trapping e.g. when moving the cellpointer, with something like:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    [calcModeCell].Calculate
    
    End Sub
    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    Dick-Y (2013-05-13)

  8. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Thanks Maud and Zeddy. To your point, Zeddy, it's not that I'm selfish; but, what I'm asking for deals with spreadsheets that I use here at home only (i.e., I don't share them, as I am retired and not interacting with anyone in work etc.)

    As a f/u, could Maud's suggested code be put in Book1 or somewhere else such that all my Excel worksheets would open in Calc mode.

    Best,
    Dick

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Dick

    I knew you aren't selfish. But others might be!
    I imagine that you want to turn calcs off because of the complex nature of your workbook.

    But did you know that you can turn calcs Off for specific sheets within a workbook???
    For example, if your workbook has ten sheets, you can set each sheet to have it's own calc mode setting.
    To do this, you need to see the sheets properties in the VBA window:
    Press [Alt][F11] to switch to the vba editor.
    Press [Ctrl][R] to View the project Explorer, which will show all the sheets in your workbook.
    Press [F4] to view the Properties pane for the selected sheet.
    You will see halfway down the list an option that says EnableCalculation. and alongside it a dropdown that lets you choose true or False.

    But to answer your specific question, yes, you are working in the right direction. You can have Excel open up a workbook automatically when you start Excel if you place the file in the XLStart folder. So you could place a workbook with Maud's code in the XLStart folder.

    For info on the XLStart folder, try this link:
    http://www.officetooltips.com/excel/...matically.html

    zeddy

  10. The Following User Says Thank You to zeddy For This Useful Post:

    Dick-Y (2013-05-13)

  11. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    This will reset the calculation mode back to automatic when the workbook closes
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.Calculation = xlAutomatic
    End Sub
    
    Private Sub Workbook_Open()
        Application.Calculation = xlManual
    End Sub

  12. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Alouso (2013-05-20),Dick-Y (2013-05-14)

  13. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Thank you Maudibe.

  14. #10
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Thank you Maudibe,

    You made my life a lot simpler

  15. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    IF you use Maudibe's code to turn calcs back to automatic when you close the workbook, it will mean that, for a complex worksheet set to manual calc mode, the file will always re-calculate before you can close it, which may not be what you want.
    You will also be prompted whether you want to save changes to the file (since the re-calc will 'change' the file).
    So if you forgot to save the file while in manual mode, you cannot now re-create that status.
    So be careful with what you want to do.

    zeddy

  16. The Following User Says Thank You to zeddy For This Useful Post:

    Dick-Y (2013-05-21)

  17. #12
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Zeddy, thanks for the tip, but a recalculation is exactly what I want to happen. I enter a series of data based on values of multiple cells. Using manual calculations prevents updating until I have all the new data entered based on those values. Then I often forget to manually calculate before I close and this will do it for me. Even if I open the book and make no changes, I still don't mind being prompted for a save.

    A.

  18. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Just to clarify:

    If you want the Workbook to open in manual calculation mode but recalculate before Save then use the following code by itself:
    Code:
    Private Sub Workbook_Open()
        Application.Calculation = xlManual
    End Sub
    If you want the Workbook to open in manual calculation mode but not recalculate before Save then use the following code by itself:
    Code:
    Private Sub Workbook_Open()
        Application.Calculation = xlManual
        Application.CalculateBeforeSave = False
    End Sub
    After manually setting the the calculation to manual, use the following code alone to ensure that the workbook gets updated and will open the next time in automatic calculation mode
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.Calculation = xlAutomatic
    End Sub
    Sorry if it appeared that I was suggesting to use the Workbook_Open and Workbook_BeforeClose events together.

    Maud

  19. The Following User Says Thank You to Maudibe For This Useful Post:

    Dick-Y (2013-05-25)

Posting Permissions

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