Run a macro when any (& every) workbook is opened?

[Ken Puls]

Hello,

I was wondering if there is a way to run a macro whenever any workbook is opened? I know how to code a regular workbook_open event, but I want it to be a little more robust.

Specifically, what I'm after is whenever a workbook is opened, to have VBA evaluate if calculation is automatic/manual, and advise the user if it is manual.

The reason for this is that I have a (purchased) add-in which automatically sets the calculation to manual (no option to change). If you have it open, then open another workbook, then save the 2nd workbook, it saves it with calculation set to manual. Naturally, you never check, and it caught me with a huge budget file!

Does anyone know if this is possible to do? I can write the desired code to execute it, I just don't know how to start it!

Thanks,

 

[TommyGun]

Here is a great write-up from Chip Pearson on how to capture Application Level Events. This would be the only way to do what you are looking for. If you have more problems, let us know.

 

[Ken Puls]

Thanks Tommy,

I thought that there must be a way... I'll play with this and post back if I have any issues.

Cheers,

 

[Travis]

You could also do this:

Put this in your personal.xls under
VBAProject
->Microsoft Excel Objects
-->ThisWorkbook

Sub workbook_open() 
    'Your Code Here
End Sub

But it would only run for the first time you open Excel, if Excel was already open and you open another workbook, it would not run

 

[Ken Puls]

Okay, following Chip Pearson's example, I've coded this:

Personal.xls!ThisWorkbook:

Dim AppClass As New EventClass

Private Sub Workbook_Open()
    Set AppClass.App = Application
    Call CreateMenu  'calls my utilities menu
End Sub

Personal.xls!EventClass (ClassModule):

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If Application.Calculation = xlCalculationAutomatic Then
        MsgBox = ("Calculation for this workbook is set to automatic!")
    ElseIf Application.Calculation = xlCalculationSemiautomatic Then
        MsgBox = ("Calculation for this workbook is set to semiautomatic!")
    Else
    End If
End Sub

When I open Excel, I get the error:
Compile Error: Function call on left hand side of assignment must return variant or object It seems to highlight the Msgbox= in blue (first one) although when you click okay, it also highlights the entire private sub line in yellow. (I've never seen the debugger highlight in blue before!)

Tommy, do you know what I've done wrong (or haven't done?)

Thanks,

 

[Travis]

I think you just need to ditch the "=" in your msg box call

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If Application.Calculation = xlCalculationAutomatic Then
        MsgBox ("Calculation for this workbook is set to automatic!")
    ElseIf Application.Calculation = xlCalculationSemiautomatic Then
        MsgBox ("Calculation for this workbook is set to semiautomatic!")
    Else
    End If
End Sub

 

[Ken Puls]

I think you just need to ditch the "=" in your msg box call

Doh!

This is why my tag line is what it is!

Thanks! It works perfectly now!

 

[Ken Puls]

Okay, it works fine most of the time...

The code above is all in my personal.xls file, and runs fine when I open excel normally. Any time I open a new file, it runs, and returns the desired results.

What's weird is that if I open an excel data file directly (right click and open), I get a 'Run Time Error 13, type mismatch on the first line of the App_WorkbookOpen procedure.

I can't figure out why this is. If the data file is opened first, the personal.xls, the App_WorkbookOpen shouldn't run, should it? And if personal.xls is opened first, why is it bombing in this case?

I can make a workaround by dropping in some error handling to tell the user to quit Excel then open Excel first, file second, but this seems like a big pain... I'd rather deal with the problem correctly, can anyone help?

Thanks,

 

[vbahero]

Doh!

This is why my tag line is what it is!

Thanks! It works perfectly now!

So cool to see you learning all of this back in '03 and now you're an Excel MVP. Congrats on the accomplishment!