I have a very complex workbook which has been running trouble free since 2001 but now we're migrating to Excel 2007. I have created a CustomUI XML file which creates a custom ribbon. But now my Workbook_Open event fails. I have reduced this event to just a few lines but the moment I refer to a range name it fails. I know all the range names in the workbook are held in the XML files which are triggered when you open the workbook and I think there is some conflict with the Workbook Open event triggering before the XML files have fully run and therefore the range name is not recognised.

Private Sub Workbook_Open()
Application.DisplayFormulaBar = False
Sheets("Control").Activate
With ActiveSheet
If .Range("HOLFLAG") = 1 Then
MsgBox "Do Things"
End If
End With
End Sub

The routine is far more complicated than this but I have pared it down and find the moment I refer to a range name, it fails with Run Time Error 57121 - Application-defined or Object-defined error. Never had this problem in Excel 2000 or 2003.

I have changed the range name "HOLFLAG" to the cell reference and it appeared to work. BUT… it was coming up with the wrong answer. How bizarre. There is definitely a problem with range names and IF statements at start up but if I run the routine after start up it runs OK in all cases.

Anyone any ideas.