Workbook_open () will not execute when opening an Excel 2010 file on a sheet with conditional formatting

Question

I have a book called travels.xlsm.

In this book, 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 the UDF 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 uses my user-defined function - the substring Workbook_open() will not be executed.

When I close the book when the active sheet does not use this function in conditional formatting, save and open it again - it runs as expected. It's a bit strange.

I do not want to close the book on a specific sheet or call Workbook_BeforeClose() sub to activate this "safe" sheet before closing the book.

Anyone who has an idea for a solution? Is this a bug in excel?

Answer 1

For a similar discussion, see: Excel Workbook macro macros do not always start

I think the best answer is the one given below in the commentary to one of the answers:

I came up with a solution considering the answer to a similar link. The private boolean variable in the workbook is used to determine if the workbook_open event was fired. If I have not added handlers to workbook_activate and workbook_sheet_change and restart workbook_open if the variable has not been set. - DrMarbuse

Even easier, if possible, just move your code to the Workbook_Activate event. I did this in the past when I had problems with Workbook_Open and not burning (I don't think I ever figured out why this is not).