+ Reply to Thread
Results 1 to 25 of 25

Excel turns of Automatic Calculation automatically

  1. #1
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Question Excel turns of Automatic Calculation automatically

    Hi all,

    I've got a problem with Excel which has been bugging me for months. First I thought it was system specific or version specific, but in the meantime I've switched employers and with that from Excel 2007 to 2010 and the problem is still. I've read many possible solutions, but they all seem to lead to macro based problem which this is not. I can reproduce the problem very easily so I'll describe them below. The problem is as the title states that Excel chooses to switch to Manual Calculation upon reopening a document when saved in a specific, though regularly used way (in my case that is). These are the steps:


    - Make sure no workbooks are open and open a new workbook. By default it opens with 3 sheets, otherwise add some.
    - Place on any cell of each sheet some text/data/whatever so that a printjob won't ignore the page while printing.
    - Select all sheets (if you don't know how to do this you probably can't help me)
    - Print these sheets (choose a pdf printer to not waste any paper) by default settings. (All selected will be printed as one document)
    - After that close Excel, without deselecting the sheets or anything. The program asks you to save; do that to whichever location.
    - After Excel is closed, reopen the document. You'll notice all sheets are still selected.
    - Check Calculation Options: it has been switched to manual. (At least in all my experiences)

    Let me be clear: I do not want Excel to do this.

    Does someone have a solution for this? My apologies if this had already been discussed; I couldn't find it.

    Thanks in advance for thinking with me


    EDIT: edited the first step to clarify that no workbooks should be opened
    Last edited by Plaus; 06-06-2013 at 06:16 AM.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Excel turns of Automatic Calculation automatically

    Plaus,
    I have the same issue.
    I normally work on codes, so have lots of codes on personal workbook.

    Lately, I have noticed this behaviour on my PC. All a sudden my calculation is turned in to manual. When I do calculation, the formula do not work(Become static). Initially I thought this is a one off, but becomes quite frequent.
    This behaviour usually happens when I download people's workbook to work on a code and the workbook comes with "Privacy option warning". I normally go to excel options to get rid of this message, but littlie did I notice my calculation has also changed in to manual.
    It is really weird problem and do not notice it until I do calculation.
    I do not know the reason excel behaves this way and do not know how to rectify it.
    Hope others may know the solution

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Excel turns of Automatic Calculation automatically

    Doesn't do that for me. Followed your instructions exactly. I have a pdf printer set up on my laptop so that I don't try to print while I'm out so I do this sort of thing all the time.

    Sure you don't have a workbook somewhere with calculation set to manual? Opening one in that mode can set the scene for other workbooks you open and then save.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Excel turns of Automatic Calculation automatically

    Thanks Trevor,
    I normally do not have any book open when I download samples from sites, but I have my personal workbook open all the time, so I need to check all the codes I have on my personal workbook which may trigger this issue.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Excel turns of Automatic Calculation automatically

    Try renaming your personal workbook so that it doesn't open when you start Excel.

    Then open Excel and check the calculation mode.

    Now follow the process outlined above and see what happens.

    Could be that a routine in your personal workbook sets calculation to manual and then forgets to put it back the way it found it.

    Regards, TMS

  6. #6
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    AB33, Thanks for your reply even though you haven't got a solution.

    TMS,
    I do not have any other workbooks opened. Before writing in my workbook I checked whether it was set to automaticl it was. Right before closing and saving I checked and still it was set to automatic.
    I tried it on my personal netbook as well with Office 2013 on it and same result. This makes 3 office versions (2007, 2010, 2013) in which it happens.

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Excel turns of Automatic Calculation automatically

    @Plaus

    what add-ins do you have installed and do you have any files in either of your XLSTART folders?

    what you describe is not normal behavior for excel
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  8. #8
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    Hi JosephP,

    It's just a regular installation of Excel on three systems of which I have recreated the problem: 2 times Excel 2010 installation (both me and my colleague) and 1 time Excel 2013 on my personal Netbook. As I said I had the same problem with my former employer which had office 2007 installed. At that point I hadn't tried the steps as I described above that, because I thought it was an isolated incident.

    These are the add-ins installed on my workstation:
    addins.png

    I'm not quite sure what you mean with XLSTART folders



    Maybe what might be different from normal installations is perhaps the pdf printer I have installed which is the same variable for all three stations : CutePDF

    EDIT:

    I've found the XLSTART folder in Program Files and it's empty
    Last edited by Plaus; 06-06-2013 at 05:30 AM.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Excel turns of Automatic Calculation automatically

    Try uninstalling CutePDF and installing Bullzip Printer instead. Works well for me and, let's be honest, if this only happens when you print PDF files and all the systems have CutePDF, where does that point?


    Regards, TMS

  10. #10
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    Hi TMS,

    I would seem very logical indeed. I hadn't tried that on this system to be honest, because I didn't have CutePDF at my former employer, but FreePDF. However I never tried Bullzip so I tried your suggestion and uninstalled CutePDF and installed Bullzip Printer. Same results though...

    EDIT: In addition I have wasted some paper by trying an non-PDF printer and it has the same results as well. To all environmentalists: I'll reuse it for my sketches

    Did you try my steps without having any opened documents? And thus without any opened documents with Automatic Calculation selected
    Last edited by Plaus; 06-06-2013 at 05:52 AM.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Excel turns of Automatic Calculation automatically

    I invariably have workbooks open. It's what I do for a living. Unfortunately, I'm not really in a position to shut everything down to try to replicate a problem on my system that I know that I do not experience.

    As JP says:
    what you describe is not normal behavior for excel

    Regards, TMS

  12. #12
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    I respect that and I didn't clarify that I hadn't any workbooks open before taking these steps: I'll see if I can edit the post for all first time readers.

    I experience this because I often produce documents from excel set up on multiple sheets. To make sure page numbering in the footer continues over all sheets I need to print them together otherwise it'll start at nr 1 for each sheet.
    It so happens that after printing the document and closing including saving I realize after reviewing that I forgot to change variables. I need to force myself to check whether calculation is set to Automatic before I print the changed document, because otherwise I may have changed the variable, but the solution is the same.

    However I may "forget" to do this when under time pressure which I'm sure you experience from time to time and this is not easily spotted since the calculations are quite heavy (Structural Engineering business).


    EDIT:

    Indeed the problem doesn't appear when another workbook is left open.
    Also after closing and saving the printed workbook and closing a left open notebook after that Automatic Calculation stays selected.

    It solely happens when following the steps on a single opened and close document.

    Maybe I should look into that as for maybe a temporary solution
    Last edited by Plaus; 06-06-2013 at 06:24 AM.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Excel turns of Automatic Calculation automatically

    I'm sorry, I can't really suggest anything else. It looks as though this cloud is travelling with you. Be careful people don't start to call you Schleprock.


    Regards, TMS

  14. #14
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    Don't worry. Thanks for thinking along though. I won't be called Schleprock soon I think since my working environment is Dutch (I never heard of it) :-) and I'm not the only one in the office with the problem. However I seem to be the only one to be considering a problem and trying to find a solution for it

  15. #15
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Excel turns of Automatic Calculation automatically

    Trevor,
    Thanks for your suggestion!
    I will have a go, but curious though, when I set up calculation to manual on my codes, I normally turned back in to auto calculation before the end of the macro. There must be a culprit code which probably missed the last bit switching back to auto, so the calculation still remains manual.

  16. #16
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Excel turns of Automatic Calculation automatically

    Quote Originally Posted by Plaus View Post
    I've found the XLSTART folder in Program Files and it's empty
    there are 2 XLSTART folders

    I have repeated your steps with no workbooks open to start with and cannot replicate your problem

    Edit: I retract that. there were two corporate add-ins open. after disabling them from starting I can replicate the behavior you describe-will research it
    Last edited by JosephP; 06-06-2013 at 07:21 AM.

  17. #17
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    Too bad... Thanks for trying though!

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Excel turns of Automatic Calculation automatically

    also tested in xl2003 and the same behavior occurs

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Excel turns of Automatic Calculation automatically

    If I recall correctly, Excel will set its calculation mode to the calculation mode of the first workbook opened. That will remain so until the mode is changed. It doesn't seem to change if subsequent workbooks weren't set to manual calculation.

    So, if the first workbook opened is set to manual calculation, that's Excel's mode for the duration. If you now create a new workbook, and save it, its calculation mode will be manual too ... and so it may propagate.

    It may be that one or more of your add-ins or personal workbook has been set to manual calculation.

    I really am guessing here and I don't want to push it too hard!

    Regards, TMS

  20. #20
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    Hi Trevor,

    To be clear of what I do; in every step up until closing excel and thereby saving the document I make sure the mode is set to Automatic. Only right after closing Excel and with that the document it somehow changes the document its setting to manual which is apparent when I reopen it. If instead I open a new workbook it's Automatic calculation by default

  21. #21
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Excel turns of Automatic Calculation automatically

    did y'all see my edit to my earlier post? I can replicate in 2003 and 2010 if no workbooks, including add-ins, are open

  22. #22
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    Hi JosephP,

    I saw that yeah only after posting myself.

    I took the liberty of recording it on my netbook. My administrator didn't allow me to instal a piece of software on the corporate computer. It's in Dutch but if you know the program you should be able to recognize the commands.

    http://www.paulpoelwijk.nl/screencap.avi

    Be patient... My computer doesn't run that fast when it's recording :P

  23. #23
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Excel turns of Automatic Calculation automatically

    don't need to watch it since I can reproduce it :-)

    seems like a bug but given nature of the problem I doubt you'll see a fix. a workaround would be to have a startup macro workbook that sets calc to automatic

  24. #24
    Registered User
    Join Date
    06-06-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Office 2010 & Office 2013
    Posts
    10

    Re: Excel turns of Automatic Calculation automatically

    Hi JosephP,

    Thanks for the idea. However I think I'm going to suggest colleagues to always make sure they don't save file when having selected multiple sheets. This has a second advantage, because it will reduced the risk of someone (else) ruining the sheet when editing some values being unaware of the fact that multiple sheets are selected. After making sure only one sheet is selected after printing there's no problem with the setting anymore.

    I share your view that there might be no fix at all. Believe me it's been bugging me for a long time and I've searched a lot for the solution never to have found it. Only today I had narrowed it down by accident to the fact that a few actions on my part which made it possible to indentify what the problem was.

    Thanks for your help!

  25. #25
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Excel turns of Automatic Calculation automatically

    I tried to recreate this scenario 2002, and it would not switch to manual. Perhaps I missed something in the procedure, or something different in my installation, or perhaps the bug was introduced in 2003.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1