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!

 

[Leith Ross]

FYI,

Crossposted Here a few minutes before being posted here.

 

[learningVBA321]

That is correct, I posted on two different sites, is there an issue with doing so? I really need a quick answer, but it seems that did not help me either, as noone has replied with any help. Does anyone have anything to offer on this?

 

[learningVBA321]

Okay, so here is the actual error I am getting. It runs fine if I hit the 'continue' button in VBA editor. So the code is right, it is just executing too early, I think? How can I get it to run right after the wkbk opens?

Thanks!

-- removed inline image ---

 

[learningVBA321]

Okay, so I do not know what I am doing, I cannot edit my last post and I cannot insert an image because it is not a URL. It is just a snip. Sorry!

 

[Jaafar Tribak]

We sometimes have people send us files with formulas set to manual calculation

I am not sure I understand but Calculation is not a Property of the workbook. It is a Property of the Application object. so how can the sent workbooks be set to manual calculation ?! unless they set it via VBA code when they are opened.

Anyways, this is just a stab in the dark but you could try introducing a brief delay before running the code you have posted as follows:

In the Thisworkbook module of the Personal workbook:

Private Sub Workbook_Open()
    Application.OnTime Now, Me.CodeName & ".SetCalcMode"
End Sub

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

 

[learningVBA321]

I cannot edit, so posting this up, I do not want to be wasting anyone's time!!

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.excelforum.com/showthread.php?p=4694104

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



[/URL]

 

[learningVBA321]

Hi Jaafar! So that is what I thought too, but then realized that MS handles this in a specific fashion, even thought it is application-specific. Take a look here: https://support.microsoft.com/en-us/help/214395/how-excel-determines-the-current-mode-of-calculation

 

[learningVBA321]

Jaafar, you are amazing, thank you! Your code worked flawlessly!! No errors, and it changed the manual setting to automatic! Perfect!

I will post this in my cross-posts, but wanted to ask another question. In the ThisWorkbook, 3 users already have a workbook_open event, where they call to another macro which is in a class module.

They need to have both of these actions execute. When I combine your code with this 'Call' command, I get a compile error: sub or function not defined. I tried to run the call as a separate private sub but excel will not allow two workbook_open subs. Do you have a suggestion on how to make this work, to have both your command and the call command launch on opening? These are all in the Personal wkbk.

Thank you thank you, thank you again!

 

[Jaafar Tribak]

Is this problem on your pc only or on other users pcs as well ?