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)

Answer 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.

Answer 2

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...

Answer 3

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.

Answer 4

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.

Answer 5

A few suggestions:

1. Try signing the workbook with a digital certificate. Add this certificate to the Trusted Certificates store then try again.
2. If this is machine-specific, try re-installing Office.

Make sure you have the latest service pack(s) applied.

Answer 6

I encountered the same problem, and I avoid it using the security settings. I use the options settings then confidentiality center, then "params of confidentiality center" (sorry but its a translation of the french version :-p) then "files approuved" or something like this. And add the file containing the excel workbook in. And its finnaly worked after that.

Looked everywhere and never find that solution.

Hope it'll help someone

Answer 7

This happens when a workbook is closed with an Application.EnableEvents set to false, and then you open another workbook within the same instance of excel opened. To avoid this, make sure that all of your processes that disable events, reenable them before terminating. Special attention to "End" commands, error handlers and "exit sub" sentences in the middle of your program.

Answer 8

What causes it is that your other archive, the one you openned first, have a Workbook_Open procedure; Excel doesn't excute it a second time.

Answer 9

This happened to me also and took me hours to figure out.

Turns out the TODAY() function in Excel was causing the problem. Once deleted from my worksheet everything worked again. Very strange bug.

Answer 10

To add to the Arturo Llano post: The following code was used to monitor the Workbook_Open event and then run ProcessX whenever a workbook was opened.

ProcessX contained an End statement. The result was that it worked only the first time. The End wiped out AppX, so there was no further monitoring of events. Removing End fixed the problem. (Using End is bad practice anyway as it stops everything without any kind of cleanup or termination of other resources).

'Code in: Personal.xlsb ThisWorkbook

Public WithEvents AppX As Application

Private Sub Workbook_Open()

   Set AppX = Application

End Sub

Private Sub AppX_WorkbookOpen(ByVal wb As Workbook)

  'A 1-second delay to allow opening to complete before ProcessX starts.

   Application.OnTime Now + TimeValue("00:00:01"), "ProcessX"

End Sub