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!

 

[Jaafar Tribak]

Not sure but try placing this in the ThisWorkbook Module of your Personal workbook and save it :

Option Explicit

Private WithEvents app As Application

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_NewWorkbook(ByVal Wb As Workbook)
    Application.OnTime Now + TimeSerial(0, 0, 2), Me.CodeName & ".SetCalcMode"
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    Application.OnTime Now + TimeSerial(0, 0, 2), Me.CodeName & ".SetCalcMode"
End Sub

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

Before the code takes effect, you need to close the application and reopen it

Note that you can try this same approach in an addin which I prefer over using the Personal workbook.

 

[learningVBA321]

It is on their computers, but I can duplicate the same response on my machine. Here is how it looks. It is the Call Delete_Run. You can see here that I tried the workbook_openafter as well, just to see what would happen. No error, but it does not execute the call either.

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

Private Sub SetCalcMode()
    Application.Calculation = xlAutomatic
  End Sub
  
Private Sub workbook_openafter()
   Call Delete_Run
End Sub

I tried it with the call line in the other subs as well, but no joy. Just been trying different combinations.

 

[learningVBA321]

Nevermind, I figured it out, they had the delete-run re-named in the class module! Augh. I was able to reproduce it on mine because I had just imported their file to my own machine. I went back through and saw this. Not sure how they were getting it to run beforehand (who knows, maybe they had not even checked for a while)!

Again, thank you so so much Jaafar!!! I am not sure how to give you any sort of reputation, if there is a mechanism please let me know!

 

[learningVBA321]

Okay Jaafar, so I spoke too soon about there being no errors. I tried all day to re-write the code using onerror, trying in different modules, etc, just so I would not have to bother you again, but I give up. So everything works just as I said when they open a file.

However, for some reason, when they go to open up a NEW blank wkbk, they get the VB Error 400. This does not happen when they just open a file. Only when they open from scratch, and it sits in the Open screen. I even set the code to delay a few seconds, so if they opened a new wkbk fast enough, it executes as it should, with the wkbk open.

I also got the error to go away with onerror, resume next but the weird thing is, if the macro executes before you open a new wkbk, it works in the opposite way, changing the calc to manual! Why would it do this? Is there a way to get it to execute correctly without the 400 error? I am just worried my users will go to open a new wkbk and then wait too long to open it (say someone walks up and starts talking and they look away) and boom, they are in manual calc. I could set the delay to 30 seconds I guess, but I would like to eliminate the issue altogether.

It is at this screen: Edit: apparently we can only insert pics from URLs, so cannot show the pic, but it is at the first open screen when you first launch excel.

 

[Jaafar Tribak]

Are you using the code in post#11 ?

 

[Jaafar Tribak]

Here is another approach to brute-force excel calculation to remain automatic throughout the excel session no matter what .. even if the user voluntarly tries to change this setting.

Again, try palcing the following code in the ThisWorkbook Module of the Personal workbook, save the code, close excel and reopen:

Option Explicit

Private WithEvents cmb As CommandBars

Private Sub Workbook_Open()
    Set cmb = Application.CommandBars
End Sub

Private Sub cmb_OnUpdate()
    If Application.Calculation <> xlCalculationAutomatic Then
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub

Not a nice workaround but let us see if this prevents excel calculation mode from weirdly going back to manual.

 

[learningVBA321]

Hey Jaafar, so a couple of things. First, I just tried the cmd code you just posted but got a debug error for a type mismatch on this line:

If Application.Calculation <> xlCalculationAutomatic Then

However, you were correct again in that I was not using the code form post 11. So I went and put that code in. I still got the code error 400, and it would make new wkbks open in manual. However, I see that in your code, you set it to run redundantly, so it runs at the new wkbk and then again once a wkbk is open, with 2 second delays. Which solves the issue of them going to manual. So I just added an onerror line to get rid of the error 400 msg and now it all seems to be working, no matter how we open them!

Thanks again, you are most definitely the man!!!!! I am going to credit you on my cross posts which are linked to in my first post so you can see them, and will post up your code as the solution (from post 11).

 

[Jaafar Tribak]

Hi learningVBA,

I am glad you finally had this working for you and thanks for the feedback