Excel 2007 'Corruption'- Named Ranges vanising, buttons losing Macros

Question

I'm seeing a lot of weird things happening in some excel sheets I use.

Most of the sheets are saved in xlsb

Has anybody else seen behaviour like this or have any idea what causes it?? Dave

I have update from Office 2003 to 2007 and now I am having this issues as well. My code creates a new file in Office 2007, then copies a sheet from the 2003 upgraded file to the new file. So technically the file is new, but the sheet had been upgraded from a 2003 file. Without reason and without warning I note 2 named ranges get removed (there maybe more, but I have only stumbled on these 2). The named range is for a single cell and contains a formula. This happens more often than not when I create the new file, but its always the same to named ranges. Ideas?

David Hayes · Accepted Answer · 2010-01-07 16:59:54Z

up vote 1 down vote accepted

I found the solution! It's a bug in Excel and there is a hot fix here Excel HotFix

answered Jan 7 '10 at 16:59

David Hayes
8572812

Ouch, lots of stuff in there. Moral of the story, update your MS Office! – mtone Feb 2 '10 at 22:09

Vonno · Answer 2 · 2010-03-10 22:18:37Z

I have found that named ranges in macros needs to be adjusted. MS Excel 2003 had this as a sample code

Columns("E:E").Select
ActiveWorkbook.Names.Add Name:="Temp2", RefersToR1C1:="='End-2-End'!C6"

MS Excel 2007 has this as a sample code.

Columns("E:E").Select
ActiveWorkbook.Names.Add Name:="Temp2", RefersToR1C1:="='End-2-End'!C5"

This simple change from C6 to C5 is the solution. Once those updates were made in the macros, Named Ranges worked again for me.

2 Answers