Excel Workbook Open Event macro doesn't always run

Question

I've got a Workbook_Open event macro (and it's in ThisWorkbook) that doesn't always run.

• If Excel is closed and I double-click the .xls file from Windows Explorer, it does run.
• If I launch Excel from Start/Programs (with no .xls file) and then open the file, it does run.
• If I've already opened the file in Excel, but then close the file (leaving Excel open) and reopen it, then the macro does not run.

I've got security set to medium and I enable macros whenever it opens.

What do I need to do to get the macro to run whenever I open it, not just the first time for this Excel session?

(btw, this is Excel 2003)

Solution 1

I thought that this was the most cogent article on this problem (which is a long-standing never explained completely erratic bug that Excel exhibits). (dead link)

In short, in many cases it's a timing thing caused because the workbook is trying to calculate stuff when it opens and that gets in the way of the Workbook_Open event. The way to test on yours to see if that it for this situation, is to first rename any UDFs or Macros called by cells so that they won't get called and see if that changes anything.

Solution 2

A late answer (better than none).

I've had this problem now a few times (with Excel 2010). The solution that has always worked (so far) was: remove conditional formatting, in particular if it contains UDF as conditions. As @LanceRoberts wrote in an above post, it's ultimately due to UDF calculations "overriding" the Open event, but I've found that those are particularly harmful if used in conditional formats.

Solution 3

I was experiencing almost identical behavior, and found that it is due to a bug that occurs if conditional formatting rules are erroring out. It turns out that if the conditional formatting rules are based on any setup by the macros, and that causes the conditional formatting to error, the Workbook_Open macro will not even attempt to run.

To test, make a copy of your file, and delete all conditional formatting from the workbook. Save and reopen. If it fixes your issue, then rework the conditional formatting rules to not depend on functions/values that will be broken before the Workbook_Open macro runs.

Solution 4

I experienced the same problem.

I tested a workbook on my computer without any troubles. After destributing it to my customers I was told, that some combo-boxes stayed empty. These are usually filled from inside the workbook_open routine. I tried different things to enable the workbook_open-Event - without success.

Finally, I found that disabling all userdefined Functions (UDF) lead to correct execution of workbook_open.

As my workbook is opened from another file, I will try to set calculation to manual first and then run the workbook_open manually. This may be done by defining it

public sub workbook_open

instead of

private sub workbook_open

Strange, that excel does not time this by itself...

View more solutions

Comments

[Tiago Cardoso]

Maybe changing the calculation to manual couldn't do the trick as well?

[ScottSM]

I just tried adding the code from the article you mentioned; didn't seem to help. :-( And there's a whole lot of tabs and a lot of code...hard to rename everything...any suggestions on how to start?

[ScottSM]

Oh -- but thanks very much for posting the link to that article! Good to know that I'm not alone in this!

[Lance Roberts]

@ScottSM, I'd see if I could find a way to completely disable calculation when the worksheet opened up and see if that helped. I'm not sure off-hand how to do this when the Workbook Opens, but maybe some research will turn up a way. You might be able to copy one Tab at a time to a blank workbook, and see if any particular one is causing the Workbook_Open event not to fire. Of course with interrelations between sheets, that's not always so easy.

[DrMarbuse]

I came up with a solution considering an answer to a similar link. A private boolean variable in the workbook is used to determine if the workbook_open event was fired. If not the I added handlers to workbook_activate and to workbook_sheet_change and rerun the workbook_open event if the variable was not set.

[Bigtree]

But I need My conditional formatting. How can I keep it from doing it's thing until after the file is loaded.

[assylias]

Spot on - removing UDF used in conditional formatting solved it for me.

[Ira Burton]

There appears to be no way to do that. Again, if you wright your conditional rules to not depend on those values which are macro dependent (which can typically be done if you think through the problem, but is specific to each case) you can get the issue resolved. Deleting the conditional formatting is just a testing step to identify the specific formatting rule creating the issue.