Excel Workbook Open Event macro doesn't always run

excel events vba excel-2003
ScottSM picture ScottSM · Mar 25, 2011 · Viewed 29k times · Source

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

Lance Roberts picture Lance Roberts · Mar 25, 2011

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.

Related questions

MSWinsock.Winsock event handling in VisualBasic

I'm trying to handle Winsock_Connect event (Actually I need it in Excel macro) using the following code: Dim Winsock1 As Winsock 'Object type definition Sub Init() Set Winsock1 = CreateObject("MSWinsock.Winsock") 'Object initialization Winsock1.RemoteHost = "MyHost" Winsock1.RemotePort = "22" Winsock1.…

Read More
How to detect if a specific key was pressed?

I'm wondering is there a way to detect if a specific key (like backspace) was pressed. This is what I'm shooting for: Private Sub SomeTextBox_Change() If len(Me.SomeTextBox.Value) = 3 and KEYPRESSED is NOT BACKSPACE Then <.......Code Here&…

Read More
Worksheet_Activate not triggering when workbook opened

Two days ago my code to populate ActiveX combo boxes in my Excel sheets stopped functioning when I open the document. I have since discovered that the Worksheet_Activate() no longer triggers when I open sheets. Now even if I …

Read More