Problem with Workbook_Open with CustomUI

birdseye42 · #1 08-26-2009, 11:59 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode