[RESOLVED] Workbook_Open event won't run

Ok, I've used Workbook_Open events many times before without problem. Now I have one that simply will not run and it's driving me crazy. I've recompiled, restarted Excel 2007, and done everything I can think of. I've reduced the code to a simple msgbox command and it's in the workbook module. The Workbook_BeforeClose event works okay. Any body have any ideas why a Workbook_Open event will not run?

[CODE]
Private Sub Workbook_Open()
MsgBox 1
End Sub
/CODE]

Thanks.

Right well it works when i do it, as i suspected it would.

I suggest that you are probably missing something else, i would probably need to see your workbook to help further though really !!!

Do your other workbooks with this event still work ? as that would isolate your problem just to this workbook !

Your Events are probably turned off. In the immediate window type: Application.enableevents = true

hit enter, close down Excel and openit and it should work.

My suspicion is the old "cut and paste a sub" issue; if you copied and pasted the whole thing, including the "Private Sub Workbook_Open()" line, then Excel can sometimes fail to link that with the actual event.

Delete the whole thing, pick the event from the dropdown so it creates a new event and try it again. I'll be surprised if it doesn't work.

This is wierd. Next day, after rebooting PC, same problem. But, I find if I close the workbook with any sheet active except the first one (on which I eventually want to run an event tha protects it), and then reopen the workbook, the event code runs. If I close with the first one active, it doesn't run. I think there is some kind of corruption in this workbook. But I even deleted every formula, value, format, etc from the sheet and it still fails. This sheet, BTW, is the one where on another post of mine, I get the 1004 error when I run the ExcessFormatCleaner.

can you rip everything out and put it in a new Workbook, and see if it works then ??

There are many sheets, dozens of named ranges and cells, and very complex formulas. I've copied the first sheet (the one that is giving me trouble), values only, to a new workbook, and something strange happens. When the original workbook is open, and I close and reopen the new test workbook, the event code in the new test workbook does not execute. If I close the original workbook, then close and reopen the new test workbook, the open event code executes. Not sure what to do next. Rebuilding the entire wb would take days.

And it's simply the msgbox code that won't run? Not with some dependency on another file or some such? That's extremely strange. What about if you run them in separate instances of Excel (ie not in the same application)?

I've already tried that. The new test wb event code works in another Excel instance.

New info, though. I deleted the first sheet, closed and reopened the wb and the event code runs. So it has something to do with the first sheet.

OK, I think I've found the culprit, or at least one of them. When I clear the conditional formatting from the first sheet, everything works (including the XSformatcleaner). When I add it back, it fails. Here's my CF code:

Code:

---

=NOT(IsFormula(B6))

---

The rule applies to a named range 'editable_values'. When a user overwrites the formula in a cell within this range with a value, the cells fill color is changed.

Was never able to find out why the CF kept the wb open event from running. I switched the CF formatting which changed the cells interior color to a worksheet change event that changed the cells interior color. Everything working now. Thanks for all your tries to help.

I know this thread is old, but I wanted to share my solution to this problem for future readers.

By circumnavigating the problem it wil not be necessary to delete the Conditional Formatting. The strategy is to always open the workbook with a worksheet without CF so that the Workbook_Open Event will be allowed to run. And then after running the Workbook_open event the workbook must automatically make the worksheet with the CF active. This can be achieved in two steps.

First make sure that the workbook always closes with the worksheet without CF as the active worksheet. In that case by default the next time the workbook will open with that worksheet active. We do that by executing the following code during the Workbook_BeforeClose event on the ThisWorkbook object.

Example:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Sheets("Worksheet_without_CF").Activate
End Sub

Second, we set the initial view of the workbook to the worksheet with the CF. This can be achieved by adding the following line of code to the Workbook_Open event on the ThisWorkbook object.

Example:

Private Sub Workbook_Open()
... your code ...
... your code ...
ActiveWorkbook.Sheets("Worksheet_with_CF").Activate
End Sub

You can add your additional code that you want to run during the Worksheet_Open Event. This solution is guaranteed to work because it completely sidesteps the CF problem of the Workbook_Open Event. The workbook will now open on the worksheet with the CF code without us even suspecting that an annoying bug had to be outwitted to achieve our goal!

If you don't need to have the worksheet with CF opened when opening the workbook then you can leave out the last line of code in the Workbook_Open sub.