Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

  1. Some named ranges vanish
  2. Worksheet buttons 'forget' what Macro they should run
  3. Named ranges 'forget' what cells they pointed too

Most of the sheets are saved in xlsb

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

share|improve this question
Have you checked for a virus? – Xavierjazz Nov 23 '09 at 16:10
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? – user25007 Jan 24 '11 at 1:22
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

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

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

ActiveWorkbook.Names.Add Name:="Temp2", RefersToR1C1:="='End-2-End'!C6"

MS Excel 2007 has this as a sample code.

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .