Workbook_open() won't execute when Excel 2010 file opened on a sheet with conditional formatting

Question

I have a workbook named travels.xlsm.

In ThisWorkbook I have this code:

Private Sub Workbook_Open()
    MsgBox "hello"
    Application.Calculation = xlManual

    ActiveWorkbook.RefreshAll   'refresh the querytables without recalculating

    Application.Calculation = xlAutomatic
End Sub

and this code in UDFs module:

Function hasHyperlink(rng As Range) As Boolean
    hasHyperlink = rng.Hyperlinks.Count
End Function

when I open the file when the active sheet contains conditional formatting that is using my user-defined function - the Workbook_open() sub won't execute.

When I close the workbook when the active sheet does not use this function in conditional formatting, save and open again - it executes as expected. This is a bit strange.

I don't want to close the workbook on a specific sheet or invoke the Workbook_BeforeClose() sub to activate that "safe" sheet before closing the workbook..

Anyone with an idea for solution? Is that a bug in excel?

Answer 1

For a similar discussion see: Excel Workbook Open Event macro doesn't always run

I think the best answer is the one below that appears in a comment on one of the answers:

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

Even simpler, if feasible, just move your code to the Workbook_Activate event. I've done that in the past when I had problems with Workbook_Open not firing (I don't think I ever figured out why it wasn't).