VBA to Open All Workbooks in Automatic Calculate

[learningvba321]

Hello, I am hoping someone can help me here, as I have been beating my head against a wall trying to make this work. Everything I have tried has failed, and usually I can figure these issues out.
We sometimes have people send us files with formulas set to manual calculation. The recipients want them to always open and re-set to automatic calculation. I have tried class modules, using this workbook, calling the operation separately, and application events. Everything I have seen has noted this to be the code, which should be put into the 'ThisWorkbook' area of the Personal folder:

Private Sub Workbook_Open()
Application.Calculation = xlAutomatic
End Sub

Seems simple enough, right? Well, every time I try to run this at the application level, I get the runttime error 1004. Which seems to indicate that the command will not run unless you have a workbook already open. But I keep seeing this work for other people, at least online. I tried to run it with the command workbook_afteropen() as well. I am just at a loss.
I tried to run it as a module with auto-run, but when I tried that, excel kept saying it was an ambiguous name. I just do not understand why this will not work. I have Office2016. Did something change in 2016 where this will not work anymore?

Really hoping someone can give me some guidance. It should not be this difficult to set excel to auto-calculate as a default, but if someone sends you a manual calc file, it sets that way. Then everything else you open after that opens in manual, which just becomes a nightmare if you were unaware. I have all macros enabled, I run macros all the time. Just this one is not working with me.

Thanks for any assistance!

Edit: so I just found out that cross-posting is an issue, sorry, had no idea! I have this in two other forums. Had no idea any of you all were connected, I was just out looking for an answer and figured I would post in as many places as I could to get as many answers as I could. I even chose the exact same name, so it should be easy to find!

Here are the links:
https://www.mrexcel.com/forum/excel-...ml#post4865188

http://www.ozgrid.com/forum/showthread.php?t=204780

[learningvba321]

Hi, does anyone have a suggestion?

[AlphaFrog]

I don't have an answer to your VBA problem, but a simple trick is to first open any workbook (it can be a template) that has automatic calculations before opening the manual calc files. The first opened workbook dictates the application's calc mode; including the calc mode for subsequent workbooks.

How Excel determines the current mode of calculation

[learningvba321]

Hello AlphaFrog, thanks for the reply. I do know how it opens and runs and I have suggested that already. However, that solution has not worked, as they often have nothing open, and then they get an email and open the attachment. Then everything else opens in manual, and they are not aware of the problem. They need something to force autocalc right from the get-go. It has to be something to do with the application.calculation command running before the first wkbk is open, as it actually runs fine if I just hit the 'continue' button in the VBA editor. So the code is right, I just need to know how to get it to execute at the right time. That is why I tried autorun, but then kept getting the ambiguous name issue.

Capture1.JPG

I have attached a snip of the actual error as it happens.

Thanks!

[AlphaFrog]

I'm spit-balling here, but try this in your Personal.xlsb file.

Please Login or Register  to view this content.

[cytop]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[BellyGas]

I'm spit-balling here, but try this in your Personal.xlsb file.

Please Login or Register  to view this content.

That'll have to go into every users personal.xlsb

On the other hand, the next user who comes to you saying can he have all workbooks opening with calc set to automatic you want to beat until he's unconscious and then brand his forehead with the words "I WILL LEARN HOW TO USE A COMPUTER AND STOP BOTHERING THE BOFH WITH STUPID BLOODY QUESTIONS" backwards so he can read it in the mirror.

Once you've done this a few times the rest will learn....

[learningvba321]

Hello, sorry, I just did the cross-post update across all the forums!

[learningvba321]

Hi Alpha, thanks so much for the code! I did give it a shot and this time I did not get an error for runtime, BUT, the code did not actually execute. Meaning, the wkbk remained in manual, instead of changing to automatic. Any other thoughts?

Thanks again, I will do that add rep thing!

[AlphaFrog]

Hi Alpha, thanks so much for the code! I did give it a shot and this time I did not get an error for runtime, BUT, the code did not actually execute. Meaning, the wkbk remained in manual, instead of changing to automatic. Any other thoughts?

Thanks again, I will do that add rep thing!

Nothing else comes to mind. If I get an idea, I'll let you know.

[learningvba321]

Hi Everyone,

Just wanted to provide an update on all my cross-posts and credit the person that got me the answer. User Jaafar Tribak came up with a couple options, but this is the one that worked best:

Please Login or Register  to view this content.

This code did produce an error 400 when launching excel from scratch and for some reason, it would actually work in reverse, setting the calc setting to manual. However, the code corrects this by executing a second time, once the wkbk is open. It is set to a two-second delay on each, which seems to work well.

To get rid of the error 400, I just added a line for error correction, so the code looks like the below:

Please Login or Register  to view this content.

Thanks again to Jaafar Tribak on MrExcel! Also to AlphaFrog on ExcelForum who educated me on just how Office handles the calculation settings and for giving the idea of using the error correction code!

[AlphaFrog]

Here's another method. I tested a little and it seemed to work.

Put this in your Personal.xlsb file in the ThisWorkbook code module.

Please Login or Register  to view this content.

If you click an Excel file's shortcut or link, that file launches Excel. With this code, Excel will stay in Automatic calculation mode regardless of the calc mode that the clicked-on-file was saved with.

The code detects if there is only one workbook open (the Personal.xlsb is workbook 1) when it is launched. If yes, it adds a new workbook before loading the clicked-on workbook. Adding a new workbook seems to "lock" in the calculation mode before the loading workbook can set it to manual because the first workbook added to Excel determines the calc mode. The code also marks the new workbook as saved and hides it. It just disappears when the application is closed.

If you simply open Excel directly without clicking on a link or a file shortcut, the code is ignored. In this instance, the Workbook_Open procedure is not triggered.

Caveat: If you launch Excel normally, it opens with the default Book1. If you don't make any changes to Book1 and then open a file that has manual calculation, the application get set to manual.

EDIT: if you don't want to allow the Caveat scenario, change If Workbooks.Count <= 2 Then

[learningvba321]

Thank you AlphaDog, you really know your stuff! I keep learning more and more through this process, understanding how some of the code works, like 'onerror' and 'ontime' and 'newworkbook' and so on. You guys are way beyond me in this realm, that is for sure!